Dedicated Health: Provider Directory and Member Data Assignment

Signing up for Mode

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. 

You have access to a database with three tables representing data as of December 31, 2018: Members, Providers, and 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?

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