AVG with CASE expression
Compute on-time delivery rate per supplier: a PO is on-time if gr_date is at most 14 days after po_date. Show supplier_name, on_time_pct sorted ascending.
| Column | Type | Key |
|---|---|---|
| po_id | INT | PK |
| supplier_id | INT | FK → dim_suppliers |
| category_id | INT | FK → dim_categories |
| contract_id | INT | FK → dim_contracts |
| req_date | TEXT | |
| po_date | TEXT | |
| ack_date | TEXT | |
| asn_date | TEXT | |
| gr_date | TEXT | |
| invoice_date | TEXT | |
| qty_ordered | INT | |
| qty_received | INT | |
| unit_price | REAL | |
| contracted_price | REAL | |
| status | TEXT |
| Column | Type | Key |
|---|---|---|
| supplier_id | INT | PK |
| supplier_name | TEXT | |
| country | TEXT | |
| tier | INT | |
| category_focus | TEXT | |
| diversity_certified | INT | |
| payment_terms_days | INT | |
| is_preferred | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
On-time-rate is a conditional ratio: turn 'within target' into 1.0/0.0 inside an aggregate.
Filter to gr_date NOT NULL.
ORDER BY on_time_pct ASC.