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 |