Date arithmetic + GROUP BY AVG; filter NULL actual_arrival.
For the supplier review: average lead time per supplier in days. Lead time = `actual_arrival - order_date` for received POs. Skip in-flight orders (NULL actual_arrival) and cancelled ones — only count POs we actually got delivered. Return supplier_name and avg_lead_time_days. Round to 1 decimal so the slide reads cleanly.
| Column | Type | Key |
|---|---|---|
| po_id | INT | PK |
| po_number | TEXT | |
| sku_id | INT | FK → dim_skus |
| supplier_id | INT | FK → dim_suppliers |
| warehouse_id | INT | FK → dim_warehouses |
| order_date | TEXT | |
| expected_arrival | TEXT | |
| actual_arrival | TEXT | |
| qty_ordered | INT | |
| qty_received | INT | |
| unit_cost | REAL | |
| status | TEXT |
| Column | Type | Key |
|---|---|---|
| supplier_id | INT | PK |
| supplier_name | TEXT | |
| country | TEXT | |
| lead_time_target_days | INT | |
| on_time_target_pct | REAL | |
| is_active | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
JOIN `fact_purchase_orders` to `dim_suppliers` ON `supplier_id`. Filter `status = 'received'` to drop cancelled and in-flight POs.
Lead time per row = `julianday(actual_arrival) - julianday(order_date)` (SQLite) or `actual_arrival::date - order_date::date` (Postgres).
GROUP BY supplier_name, AVG(lead_time_days), ROUND(..., 1). ORDER BY avg_lead_time_days DESC for readability.