Your question is Identify Supply Chain Bottlenecks by Delay Rate. Start with the requirements and the three 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.
You are given shipment data across suppliers, warehouses, and deliveries. Write a PostgreSQL query that identifies the supply chain stages with the highest bottleneck risk by comparing planned and actual dates, then aggregating delay rates by stage. Return only stages with at least 3 shipments and a delay rate above 30%, ordered by delay rate descending.
| Column | Type | Description |
|---|---|---|
| shipment_idPK | INT | Primary key for each shipment |
| supplier_id | INT | References the supplier that shipped the goods |
| warehouse_id | INT | References the warehouse handling the shipment |
| planned_ship_date | DATE | Planned outbound shipment date |
| actual_ship_date | DATE | Actual outbound shipment date, if shipped |
| stage | VARCHAR(50) | Supply chain stage name |
| Column | Type | Description |
|---|---|---|
| supplier_idPK | INT | Primary key for each supplier |
| supplier_name | VARCHAR(100) | Supplier display name |
| Column | Type | Description |
|---|---|---|
| warehouse_idPK | INT | Primary key for each warehouse |
| warehouse_name | VARCHAR(100) | Warehouse display name |