Window Functions for Enrollment Growth | Dataford Interview Questions

Write a SQL query using window functions to calculate month-over-month changes in household enrollment and rank regions by growth within each country.

Use the provided enrollment and region tables. Include only region-month rows with a prior observed month for comparison.

Output

One row per country, region, and month with a prior month
Columns: country, region_name, enrollment_month, household_enrollment, mom_change, mom_growth_pct, and growth_rank
Rank regions within each country and month by percentage growth descending, with ties sharing a rank
Order by country, enrollment_month, growth_rank, and region_name

Schema

regions

Column	Type	Description
region_idPK	INT	Unique region identifier
country	VARCHAR(100)	Country containing the region
region_name	VARCHAR(100)	Region name

household_enrollments

Column	Type	Description
enrollment_idPK	INT	Unique enrollment record identifier
household_id	VARCHAR(50)	Household identifier
region_id	INT	Region associated with the enrollment
enrollment_date	DATE	Date of enrollment

Your question is Window Functions for Enrollment Growth. 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