Top 3 Outages Per Region | Dataford Interview Questions

Your question is Top 3 Outages Per Region. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Nokia NetAct stores network outage events that operations teams use to identify recurring reliability problems. Write a PostgreSQL query to find the three longest completed outage events for every region.

Requirements

Join outage_events to regions and display the region name.
Calculate each completed outage duration in hours from started_at and ended_at.
Use a window function to rank outages independently within each region, return only ranks 1 through 3, and sort ties deterministically by outage_id.
Exclude ongoing outages, invalid timestamps, and events whose region does not exist in regions.

Schema

regions

Column	Type	Description
region_idPK	INTEGER	Unique regional identifier
region_name	VARCHAR(100)	Nokia network operating region

outage_events

Column	Type	Description
outage_idPK	INTEGER	Unique outage event identifier
region_id	INTEGER	References regions.region_id
outage_type	VARCHAR(40)	Network outage classification
started_at	TIMESTAMP	Outage start timestamp
ended_at	TIMESTAMP	Recovery timestamp, NULL for ongoing outages