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.
| Table | Column | Type | Description |
|---|---|---|---|
shipments | shipment_id | INT | Primary key for each shipment |
shipments | supplier_id | INT | Foreign key to suppliers.supplier_id |
shipments | warehouse_id | INT | Foreign key to warehouses.warehouse_id |
shipments | planned_ship_date | DATE | Planned outbound date |
shipments | actual_ship_date | DATE | Actual outbound date, nullable if not shipped |
shipments | stage | VARCHAR(50) | Supply chain stage name |
suppliers | supplier_id | INT | Primary key for each supplier |
suppliers | supplier_name | VARCHAR(100) | Supplier name |
warehouses | warehouse_id | INT | Primary key for each warehouse |
warehouses | warehouse_name | VARCHAR(100) | Warehouse name |
shipments
| shipment_id | supplier_id | warehouse_id | planned_ship_date | actual_ship_date | stage |
|---|---|---|---|---|---|
| 1 | 1 | 10 | 2024-05-01 | 2024-05-03 | inbound |
| 2 | 1 | 10 | 2024-05-02 | 2024-05-02 | inbound |
| 3 | 2 | 11 | 2024-05-01 | 2024-05-05 | outbound |
| 4 | 2 | 11 | 2024-05-03 | 2024-05-04 | outbound |
| 5 | 3 | 12 | 2024-05-04 | null | customs |
| 6 | 3 | 12 | 2024-05-05 | 2024-05-09 | customs |
suppliers
| supplier_id | supplier_name |
|---|---|
| 1 | Northwind Parts |
| 2 | Apex Components |
| 3 | Orion Materials |
warehouses
| warehouse_id | warehouse_name |
|---|---|
| 10 | Bay Area DC |
| 11 | Dallas Hub |
| 12 | Newark Hub |
| stage | shipment_count | delayed_shipments | delay_rate |
|---|---|---|---|
| customs | 2 | 1 | 0.50 |
| outbound | 2 | 2 | 1.00 |
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.
| Table | Column | Type | Description |
|---|---|---|---|
shipments | shipment_id | INT | Primary key for each shipment |
shipments | supplier_id | INT | Foreign key to suppliers.supplier_id |
shipments | warehouse_id | INT | Foreign key to warehouses.warehouse_id |
shipments | planned_ship_date | DATE | Planned outbound date |
shipments | actual_ship_date | DATE | Actual outbound date, nullable if not shipped |
shipments | stage | VARCHAR(50) | Supply chain stage name |
suppliers | supplier_id | INT | Primary key for each supplier |
suppliers | supplier_name | VARCHAR(100) | Supplier name |
warehouses | warehouse_id | INT | Primary key for each warehouse |
warehouses | warehouse_name | VARCHAR(100) | Warehouse name |
shipments
| shipment_id | supplier_id | warehouse_id | planned_ship_date | actual_ship_date | stage |
|---|---|---|---|---|---|
| 1 | 1 | 10 | 2024-05-01 | 2024-05-03 | inbound |
| 2 | 1 | 10 | 2024-05-02 | 2024-05-02 | inbound |
| 3 | 2 | 11 | 2024-05-01 | 2024-05-05 | outbound |
| 4 | 2 | 11 | 2024-05-03 | 2024-05-04 | outbound |
| 5 | 3 | 12 | 2024-05-04 | null | customs |
| 6 | 3 | 12 | 2024-05-05 | 2024-05-09 | customs |
suppliers
| supplier_id | supplier_name |
|---|---|
| 1 | Northwind Parts |
| 2 | Apex Components |
| 3 | Orion Materials |
warehouses
| warehouse_id | warehouse_name |
|---|---|
| 10 | Bay Area DC |
| 11 | Dallas Hub |
| 12 | Newark Hub |
| stage | shipment_count | delayed_shipments | delay_rate |
|---|---|---|---|
| customs | 2 | 1 | 0.50 |
| outbound | 2 | 2 | 1.00 |