SQL for Route Delay Analysis | Dataford Interview Questions

Write a SQL query to identify shipping routes that have experienced a higher-than-average delay over the past quarter at XPO.

Use the provided data for the past quarter, from October 1, 2024 through December 31, 2024. Exclude shipments with unknown delay values and return only routes whose average delay exceeds the overall shipment average.

Output

One row per qualifying route with route_id, origin, destination, shipment_count, avg_delay_minutes, and overall_avg_delay_minutes.
Order by avg_delay_minutes descending, then route_id ascending.

Schema

routes

Column	Type	Description
route_idPK	INT	Unique XPO shipping route identifier
origin	VARCHAR(100)	Route origin city
destination	VARCHAR(100)	Route destination city

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
route_id	INT	Route used by the shipment
shipment_date	DATE	Shipment operating date
delay_minutes	INT	Shipment delay in minutes; NULL means unknown

Your question is SQL for Route Delay Analysis. 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