SQL Window Ranking by Region | Dataford Interview Questions

Write a SQL query using window functions to rank clinical sites by weekly enrollment growth within each region.

Use the weekly enrollment count for each site and compare it with that site's immediately preceding reported week. Include only rows with a preceding week.

Output

One row per site and reporting week with prior-week data.
Return region, week_start, site_id, site_name, weekly_enrollments, previous_week_enrollments, enrollment_growth, and growth_rank.
Rank highest growth first within each region and week. Order by region, week_start, growth_rank, and site_id.

Schema

clinical_sites

Column	Type	Description
site_idPK	INT	Unique clinical site identifier
site_name	VARCHAR(100)	Clinical site name
region	VARCHAR(50)	Geographic or operational region

weekly_site_enrollment

Column	Type	Description
site_id	INT	Clinical site identifier
week_start	DATE	Start date of the reporting week
enrollment_count	INT	Number of enrollments reported for the site and week

Your question is SQL Window Ranking by Region. 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