Extract the hour-of-day from a timestamp and count dispatches per hour. Practice EXTRACT and grouping on a derived value to read a demand curve.
Staffing planning. I need to see when dispatches actually happen across the day. From fact_shopper_dispatches, pull the hour of dispatch_ts and count dispatches in each hour. Columns: dispatch_hour, dispatch_count. ORDER BY dispatch_hour.
| 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.
dispatch_ts is a TEXT timestamp; cast it before extracting the hour.
EXTRACT(HOUR FROM dispatch_ts::timestamp) gives the 0-23 hour.
GROUP BY that hour, COUNT(*) as dispatch_count, ORDER BY dispatch_hour.