Your question is Top Routes With Delivery Delays. 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.
Write a query to identify the top 5 routes with the highest delivery delays. Calculate each route's average delay in minutes using delivered shipments with a recorded delay. Return the results in descending delay order, breaking ties by route ID in ascending order.
route_id, origin_facility, destination_facility, shipment_count, and average_delay_minutes.average_delay_minutes descending, then route_id ascending.| Column | Type | Description |
|---|---|---|
| route_idPK | INT | Unique route identifier |
| origin_facility | VARCHAR(100) | Shipment origin facility |
| destination_facility | VARCHAR(100) | Shipment destination facility |
| Column | Type | Description |
|---|---|---|
| delivery_idPK | INT | Unique delivery record identifier |
| route_id | INT | Route associated with the delivery |
| scheduled_delivery_date | DATE | Scheduled delivery date |
| delay_minutes | NUMERIC(10,2) | Delivery delay in minutes, with negative values indicating early delivery |
| status | VARCHAR(30) | Delivery status |