Write a SQL query to analyze hub performance by joining orders, deliveries, and inventory tables to calculate daily delivery TAT, wastage percentage, and stock-out rate.
Use completed deliveries for TAT, inventory quantities for wastage, and stock-out minutes over a 24-hour operating day. Include dates represented by any source table.
hub_id, performance_date, avg_delivery_tat_minutes, wastage_percentage, and stock_out_rate.hub_id, then performance_date.| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| hub_id | VARCHAR(10) | Fulfillment hub identifier |
| order_date | DATE | Date the order was placed |
| Column | Type | Description |
|---|---|---|
| delivery_idPK | INT | Unique delivery identifier |
| order_id | INT | Related order identifier |
| dispatched_at | TIMESTAMP | Timestamp when the delivery was dispatched |
| delivered_at | TIMESTAMP | Timestamp when the order was delivered |
| status | VARCHAR(20) | Delivery status |
| Column | Type | Description |
|---|---|---|
| inventory_idPK | INT | Unique inventory record identifier |
| hub_id | VARCHAR(10) | Inventory hub identifier |
| inventory_date | DATE | Inventory measurement date |
| opening_stock | INT | Units available at the start of the day |
| received_qty | INT | Units received during the day |
| wastage_qty | INT | Units discarded during the day |
| stockout_minutes | INT | Minutes without available stock |