Top Routes by Delay | Dataford Interview Questions

Your question is Top Routes by Delay. 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

Delta Air Lines wants to identify routes with the highest average arrival delays during the last completed quarter. Assume the reporting period is April 1, 2025 through June 30, 2025. Write a PostgreSQL query that ranks routes by their average arrival delay.

Requirements

Join flight records to Delta's route reference table.
Include only flights within the reporting period and exclude cancelled flights.
Calculate the average non-null arrival delay in minutes for each route, rounded to two decimal places.
Return the top three routes, breaking ties by route_code alphabetically.

Schema

routes

Column	Type	Description
route_idPK	INT	Unique route identifier
route_code	VARCHAR(20)	Route label formed from origin and destination
origin_airport	VARCHAR(3)	Origin airport code
destination_airport	VARCHAR(3)	Destination airport code

flight_records

Column	Type	Description
flight_idPK	INT	Unique flight record identifier
route_id	INT	References routes.route_id
flight_date	DATE	Scheduled flight date
arrival_delay_minutes	INT	Arrival delay in minutes
is_cancelled	BOOLEAN	Whether the flight was cancelled