Rolling Average Outage Duration | Dataford Interview Questions

PG&E reliability analysts need to monitor how outage duration changes over time across service areas. Write a PostgreSQL query that calculates the rolling average duration of outages for each mapped PG&E service area.

Use a 30-day inclusive window, meaning each outage includes qualifying outages from the current timestamp and the previous 29 days. Exclude outage events that do not map to a known service area.

Requirements

Join outage_events to service_areas using service_area_id.
Calculate each outage duration in hours from started_at and ended_at; preserve NULL durations when an outage has not ended.
Use a window function partitioned by service area and ordered by started_at to calculate the rolling average.
Round individual durations and rolling averages to two decimal places, and order the output by service area and start time.

Schema

outage_events

Column	Type	Description
outage_idPK	INTEGER	Unique outage event identifier
service_area_id	INTEGER	References the PG&E service area
started_at	TIMESTAMP	Outage start timestamp
ended_at	TIMESTAMP	Outage end timestamp
cause	VARCHAR(100)	Reported outage cause

service_areas

Column	Type	Description
service_area_idPK	INTEGER	Unique service area identifier
service_area	VARCHAR(100)	PG&E service area name

Your question is Rolling Average Outage Duration. 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