Top Regions by Enrollment | Dataford Interview Questions

Aetna wants to identify the regional offices with the highest patient enrollment during the previous calendar month. Write a SQL query to return the top three regional offices based on distinct enrolled patients.

Output

Return one row per regional office, with columns office_name and enrolled_patients.
Count each patient at most once per office, and include only enrollment records from the previous calendar month relative to the fixed reporting date of September 15, 2026.
Return the three offices with the highest counts, ordered by count descending and office_id ascending to break ties.
Offices without qualifying enrollments may remain in the intermediate calculation but cannot appear in the top three when their count is lower.
Enrollment records with a NULL patient_id are ignored by the distinct-patient count.

Schema

regional_offices

Column	Type	Description
office_idPK	INT	Unique regional office identifier
office_name	VARCHAR(100)	Regional office name
region_code	VARCHAR(20)	Short code identifying the region

patient_enrollments

Column	Type	Description
enrollment_idPK	INT	Unique enrollment record identifier
office_id	INT	Regional office associated with the enrollment
patient_id	INT	Patient identifier
enrollment_date	DATE	Date the patient enrollment was recorded

Your question is Top Regions by 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