Conditional aggregation (CASE + AVG) for a percentage rate.
For the supplier review I also need on-time delivery rate per supplier. On-time = `actual_arrival <= expected_arrival`. Use only received POs (skip in-flight and cancelled). Return supplier_name and on_time_pct (rounded to 1 decimal). Sort highest to lowest.
| 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`. Filter `status = 'received'`.
Per-row on-time flag: `CASE WHEN actual_arrival <= expected_arrival THEN 1.0 ELSE 0.0 END`. Then AVG that and multiply by 100.
GROUP BY supplier_name, ROUND(AVG(...) * 100, 1) AS on_time_pct, ORDER BY on_time_pct DESC.