Welcome to the SQL screen.
The question is on your right: Identify Supply Chain Bottlenecks by Delay Rate. 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 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 |