You are given shipment records from a supply chain dataset and asked to identify the top 3 delayed routes within each region. A route is defined by an origin distribution center and destination market. Use PostgreSQL SQL to calculate the average delay in days for each route, rank routes within each region from most delayed to least delayed, and return the top 3 per region. Only include shipments that have both planned and actual delivery dates and where the shipment status is not Cancelled.
region_name, route_name, avg_delay_days, and route_rank.route_name as origin_dc -> destination_market.avg_delay_days to two decimal places.route_name ascending.region_name ascending, route_rank ascending, then route_name ascending.| Column | Type | Description |
|---|---|---|
| shipment_idPK | INT | Unique shipment identifier |
| region_id | INT | Region associated with the shipment |
| route_id | INT | Route used for the shipment |
| planned_delivery_date | DATE | Scheduled delivery date |
| actual_delivery_date | DATE | Actual delivery date |
| shipment_status | VARCHAR(20) | Current shipment status |
| Column | Type | Description |
|---|---|---|
| region_idPK | INT | Unique region identifier |
| region_name | VARCHAR(50) | Region name |
| Column | Type | Description |
|---|---|---|
| route_idPK | INT | Unique route identifier |
| origin_dc | VARCHAR(50) | Origin distribution center |
| destination_market | VARCHAR(50) | Destination market |