Write a SQL query to analyze warehouse performance by joining orders, inventory, and labor tables to identify bottlenecks and late shipments.
Return one row per warehouse and order date. Treat an order with no shipped date as late when it has a promised ship date. A bottleneck exists when the late rate is at least 50%, inventory shortage exists, or productivity is below 10 units per labor hour.
warehouse_id, metric_date, total_orders, late_shipments, late_rate, avg_fulfillment_days, shortage_units, units_per_labor_hour, bottleneck_flagwarehouse_id, then metric_date.| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| warehouse_id | VARCHAR(10) | Warehouse processing the order |
| order_date | DATE | Date the order was placed |
| promised_ship_date | DATE | Committed shipment date |
| shipped_date | DATE | Actual shipment date |
| item_count | INT | Number of items in the order |
| status | VARCHAR(20) | Current order status |
| Column | Type | Description |
|---|---|---|
| inventory_idPK | INT | Unique inventory snapshot identifier |
| warehouse_id | VARCHAR(10) | Warehouse holding the inventory |
| inventory_date | DATE | Date of the inventory snapshot |
| sku | VARCHAR(20) | Stock keeping unit |
| available_units | INT | Units available to fulfill orders |
| requested_units | INT | Units requested for the snapshot |
| Column | Type | Description |
|---|---|---|
| labor_idPK | INT | Unique labor record identifier |
| warehouse_id | VARCHAR(10) | Warehouse where labor was recorded |
| labor_date | DATE | Date labor was recorded |
| labor_hours | DECIMAL(8,2) | Total labor hours |
| units_picked | INT | Units picked by the labor team |