Top Peak Hours by Region | Dataford Interview Questions

Write a SQL query to identify the top 3 hours of peak demand for each region using window functions.

Use the regions and hourly_demand_readings tables. Aggregate readings recorded for the same region and hour, exclude regions without readings, and resolve equal demand totals by earlier hour first.

Output

One row per region and selected hour, with region_name, demand_hour, total_demand_mw, and peak_rank
Include only the top three hours for each region
Order by region_name, peak_rank, and demand_hour

Schema

regions

Column	Type	Description
region_idPK	INT	Unique identifier for a market region
region_name	VARCHAR(100)	Display name of the market region

hourly_demand_readings

Column	Type	Description
reading_idPK	INT	Unique identifier for a demand reading
region_id	INT	Region associated with the reading
reading_hour	TIMESTAMP	Hour represented by the demand reading
demand_mw	NUMERIC(10,2)	Demand measured in megawatts

Your question is Top Peak Hours 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