Welcome to the SQL screen.
The question is on your right: Top Delayed Routes by Region. Read through the requirements and the three tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
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.
| 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 |