Rank Uptime Outliers | Dataford Interview Questions

Your question is Rank Uptime Outliers. 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

Waymo Fleet Operations wants to compare autonomous vehicles by their daily operational uptime. Write a PostgreSQL query that aggregates completed operational intervals, ranks vehicles within each service date, and identifies unusually strong performers.

Requirements

Join vehicles to uptime intervals and calculate total operational minutes per vehicle per service date. Count only intervals with status = 'operational', a non-null end time, and a positive duration.
Use DENSE_RANK() to rank vehicles within each date by total uptime descending.
Return only vehicles in the top two dense-rank positions whose uptime exceeds that day's average vehicle uptime. Include the vehicle identifier, label, date, uptime minutes, daily average, and rank.
Order results by service date, rank, and vehicle identifier.

Schema

vehicles

Column	Type	Description
vehicle_idPK	INT	Unique vehicle identifier
vehicle_label	VARCHAR(30)	Fleet-facing vehicle label
model	VARCHAR(30)	Waymo vehicle model

vehicle_uptime_events

Column	Type	Description
event_idPK	INT	Unique uptime event identifier
vehicle_id	INT	Vehicle associated with the event
service_date	DATE	Operational calendar date
started_at	TIMESTAMP	Interval start time
ended_at	TIMESTAMP	Interval end time, null for incomplete intervals
status	VARCHAR(20)	Operational event status