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.
Output
- One row per hub and calendar date.
- Columns:
hub_id, performance_date, avg_delivery_tat_minutes, wastage_percentage, and stock_out_rate.
- Include available hub-date combinations even when one metric has no source data. Sort by
hub_id, then performance_date.
Output details
- Treat deliveries with status
delivered, requiring non-NULL dispatched_at and delivered_at, and derive their date from delivered_at.
- Use
inventory_date as the inventory performance date.
- Round all three metric values to exactly 2 decimal places.
- Calculate wastage percentage as 100 times total
wastage_qty divided by total opening_stock + received_qty, returning NULL for a zero denominator.
- Calculate stock-out rate as 100 times total
stockout_minutes divided by 1440, without clamping negative values.
- Sort
hub_id and performance_date in ascending order and retain each hub-date combination only once.
