Bucket a continuous metric into quartiles with NTILE and summarize each bucket with COUNT and AVG. Practice excluding NULLs from incomplete dispatches.
I want to understand our delivery-time spread, not just the average. Split completed dispatches into four equal quartiles by pick_to_deliver_minutes and show me the count and the average minutes in each. Skip rows where pick_to_deliver_minutes is NULL (those never delivered). Columns: quartile, dispatch_count, avg_minutes. ORDER BY quartile.
| Column | Type | Key |
|---|---|---|
| dispatch_id | INT | PK |
| shopper_id | INT | FK → dim_shoppers |
| dispatch_ts | TEXT | |
| pickup_ts | TEXT | |
| deliver_ts | TEXT | |
| batch_size | INT | |
| pick_to_deliver_minutes | INT | |
| dispatch_status | TEXT | |
| total_distance_mi | REAL | |
| shopper_payout | REAL |
Each hint you reveal reduces the XP you can earn. Try the query first.
Only dispatches with a non-NULL pick_to_deliver_minutes count; filter those first.
NTILE(4) OVER (ORDER BY pick_to_deliver_minutes) labels each row 1-4 by speed.
Wrap that in a subquery, then GROUP BY quartile with COUNT(*) and ROUND(AVG(pick_to_deliver_minutes), 1).
ORDER BY quartile so the fastest bucket is first.