SQL query interview question

Logistics: The following slides give brief background to the case and outline an analytic task. You should also have received an Excel file containing supporting data for your analysis. Please notify us immediately if you are missing the Excel file or encounter any technical difficulties. You will complete this task on your own time and will discuss your response with your interviewer the next day. Please email us your response ahead of your interview. Things to note: This case will primarily assess your analytic and coding skills, but it’s also designed to be fun and informative to help you better understand the work we do on a regular basis. You will have a full day to complete the task at your own pace; however, the task is designed to be completed in ~1 hour. Please do not feel compelled to spend significantly more time. We cannot guarantee real-time responses to content or analytics questions you may have about the case. We advise that you make reasonable assumptions and explain your assumptions to your interviewer. BMC values integrity: Please ensure that your work is your own. This is a simulation of a task you would be expected to complete with minimal guidance upon arrival at BMC. However, you are free to consult outside references (websites, textbooks) to aid in your analysis (this would be true in the job, too!) but note that you will have to explain your process, findings, and code to an interviewer. Our Medical Director wants to design an initiative to address the high inpatient spend we observe and close our medical expense deficit. She has come to you for more insight on our inpatient stays. Specifically, she has requested the following (your task): Among the inpatient (IP) stays1, design and conduct an analysis specifically for ACO members to: Perform descriptive analysis of the inpatient stays for ACO members and the ACO members themselves Identify the cost drivers2 of the inpatient stays for ACO members Calculate the readmission rate (readmission definition: an admission that occurs within a 30 day period after a prior inpatient discharge) for the ACO [Optional: identify any statistically significant disparities you observe in the data, if any] For the descriptive analysis and cost drivers, she has left the choice of variables up to you to determine what is most important In the Excel file that accompanies these slides, we have provided a (sample) medical claims dataset containing information on inpatient stays for members across multiple products (includes ACO and non-ACO) and a roster of ACO members Please choose a coding language (e.g. R, SAS, SQL, etc) and write down your code. Note: you are free to use Excel to complete the analysis, but you must write up the code you would have used in your software of choice Output your findings into tables or charts to present to your interviewer