SQL: Before and After Rollout | Dataford Interview Questions

Write a SQL query to compare regional performance before and after a phased rollout using CTEs and subqueries.

Use the provided regional rollout dates and performance records. Compare each region's 30-day period before rollout with its 30-day period beginning on the rollout date. Exclude regions without valid performance data in both periods.

Output

One row per eligible region with region_name, before_total, after_total, before_average, after_average, percentage_change, and improvement_rank.
Sort by improvement_rank ascending, then region_name ascending.

Schema

regions

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

rollout_phases

Column	Type	Description
phase_idPK	INT	Unique rollout phase identifier
region_id	INT	Region assigned to the rollout phase
rollout_date	DATE	Date the rollout began for the region

performance_metrics

Column	Type	Description
metric_idPK	INT	Unique performance record identifier
region_id	INT	Region associated with the performance record
metric_date	DATE	Date of the performance measurement
performance_value	NUMERIC(12,2)	Measured performance value

Your question is SQL: Before and After Rollout. 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