This assignment involves signing on to Mode.com to see the tables . I can provide the log in if the next steps are to be taken. I will provide the instructions below. There are 2 parts to the assignment. Part 1 is 8 simple questions(only 3 tables) in which the answers are code to query for the question. Part 2 is open ended. I have attached the instructions below. Please let me know if you are interested (I am open to negotiate).
Assignment Dedicated Health is a health insurance company offering Medicare Advantage plans. When a member joins Dedicated, they choose a Primary Care Provider (PCP), a provider who practices general medicine, from Dedicated’s provider directory of physicians, nurses, and other health care practitioners. The member’s PCP then becomes their first point of contact for health care. Signing up for Mode You’ll need a Mode account in order to access the datasets. Once you have signed up, you will get to a screen that has a link titled “Start using Mode with Sample data”. Clicking that link will take you to a report window. From there, you will have to create a new report to query the tables. To create a report click on the green plus button in the upper right corner. Then you should have access to the c_otis_anderson schema, which contains the tables you need to complete the assignment. Part 1 You have access to a database with three tables representing data as of December 31, 2018: Members, Providers, and Member_PCP_Spans. From there, you should be able to query the three tables mentioned above. They are on a publicly accessible schema, c_otis_anderson. The three tables are queryable as c_otis_anderson.members , c_otis_anderson.providers and c_otis_anderson.member_pcp_spans . The data and schema roughly resemble what we use at Dedicated, but the values are entirely fictional. Please use only SQL to answer the questions below. We expect it will take up to 2 hours to complete these questions, but it is fine if you need some more time! Please note as much in your submission. From our vendor eMedicare.com, we received a provider directory data (Providers), member directory (Members) and a table of member-provider linkage (Member_PCP_Spans), including a date span during which each linkage is effective, as well as the member’s Dedicated plan level (Core or Prime) for that month. 1. How many total entries are in the provider directory? 2. How many specialists (non-PCPs) are in the directory? 3. In which states does Dedicated provide care, according to this data? a. How many providers are in each state? 4. How many total entries are in the member directory? 5. In which states do members live? a. How many members are in each state? 6. For every individual month in 2018, how many members have a PCP? 7. How many members have a PCP in Florida in November? 8. How many members are currently (as of 12/31/18) seeing a provider practicing outside of the member’s city? Data Dictionary Table Name: Member Description: All members who were ever active at Dedicated Health Column Type Description Foreign Key id integer Unique member ID name text Member name city text City in which the member lives birth_date text Member’s birth date health_risk_score real Risk score assigned to member by the Centers for Medicare and Medicaid Services. A higher risk score indicates the member is more at risk Table Name: Providers Description: All providers contracted with Dedicated Health Column Type Description Foreign Key id integer Unique provider ID name text Provider name city text City in which provider practices is_pcp integer 1 if the provider is a Primary Care Provider (PCP), 0 otherwise Table Name: Member_PCP_Spans Description: Dedicated plan, member-PCP relationships, and their start and end dates Column Type Description Foreign Key id integer Unique member-provider link ID member_id integer Unique Member ID Member.id provider_id integer Unique Primary Care Provider.id Provider (PCP) ID plan_id integer Identifier for the Dedicated Health plan in which the member is enrolled. All members are enrolled in Plan 0 (Core) or Plan 1 (Prime) start_date end_date average_copay text Start date of the member-pcp relationship text End date of the member-pcp relationship text At the end of a calendar year, or when the member changes providers or plans, the member can optionally report the average copay they paid for visits to their provider (the provider on this span) pcp_rating real At the end of a calendar year, or when the member changes providers or plans, the member can optionally rate their provider (the provider on this span) on a scale of 1 to 5 Part 2 Part of your operations team wants to interview the PCPs who provide the best customer service to their patients, but they only have the time to interview the five best PCPs. From the same data set as you used in Part 1, which five PCPs would you recommend? Explain your reasoning. What are the limitations of your approach? What other data would you like to answer the question? Some notes: ● This question is purposefully open ended. We are not looking for one correct metric or a specific set of charts or tables. ● We ask that you limit the length of your response to 12 slides or a PDF with two pages, but do not feel obligated to pad your submission to this length. We encourage concise reports! ● Please attach any code you used to help answer the question