SQL Top Regions by Downtime | Dataford Interview Questions

Write a SQL query to find the top 3 regions by network downtime, partitioned by month.

Use completed downtime events only. Calculate each event's duration in minutes and assign it to the month in which it started. Return the three highest-downtime regions for each month, with deterministic ordering for ties.

Output

One row per selected region and month
Columns: month, region_name, total_downtime_minutes, and region_rank
Include only ranks 1 through 3, ordered by month ascending, rank ascending, and region name ascending

Schema

regions

Column	Type	Description
region_idPK	INT	Unique region identifier
region_name	VARCHAR(100)	Region name
country_code	VARCHAR(2)	Two-letter country code

network_sites

Column	Type	Description
site_idPK	INT	Unique network site identifier
region_id	INT	Region containing the site
site_code	VARCHAR(30)	Operational site code
technology	VARCHAR(20)	Primary radio technology at the site

downtime_events

Column	Type	Description
event_idPK	INT	Unique downtime event identifier
site_id	INT	Site affected by the event
started_at	TIMESTAMP	Downtime start timestamp
ended_at	TIMESTAMP	Downtime end timestamp, null for ongoing events
cause	VARCHAR(100)	Reported cause of downtime

Your question is SQL Top Regions by Downtime. 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