Window Functions for Rolling Trends | Dataford Interview Questions

How would you use SQL window functions to compute rolling survey trends and rank regions by response rate at Ipsos?

Use the regions, survey_waves, and regional_survey_metrics tables for survey waves from January through April 2025. Calculate each region's rolling three-wave response rate and its overall response-rate rank.

Output

One row per region and available survey wave.
Columns: region_name, wave_month, rolling_response_rate, overall_response_rate, and region_rank.
Include only waves in the requested period, order by wave_month ascending and region_name ascending, and assign equal ranks to tied response rates.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique region identifier
region_name	VARCHAR(100)	Ipsos reporting region name

survey_waves

Column	Type	Description
wave_idPK	INT	Unique survey wave identifier
survey_name	VARCHAR(150)	Survey or study name
wave_month	DATE	First day of the survey wave month

regional_survey_metrics

Column	Type	Description
metric_idPK	INT	Unique regional metric identifier
wave_id	INT	Survey wave identifier
region_id	INT	Region identifier
invited_count	INT	Number of invited respondents
completed_count	INT	Number of completed responses

Your question is Window Functions for Rolling Trends. Start with the requirements and the three 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