SQL for Medicare Enrollment | Dataford Interview Questions

Write a SQL query to find the top 10 states with the highest Medicare enrollment.

Use the latest available reporting month and exclude states without enrollment records for that month. Return one row per state, ordered by enrollment descending, with state name as the tie-breaker.

Output

Columns: state_name, total_enrollment
One row per state, limited to 10 rows
Sort by total_enrollment descending, then state_name ascending

Schema

states

Column	Type	Description
state_idPK	INT	Unique state identifier
state_code	VARCHAR(2)	Two-letter state code
state_name	VARCHAR(50)	State name

medicare_enrollment

Column	Type	Description
enrollment_idPK	INT	Unique enrollment record identifier
state_id	INT	Referenced state identifier
reporting_month	DATE	First day of the enrollment reporting month
enrollment_count	INT	Number of Medicare enrollees

Your question is SQL for Medicare Enrollment. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem