Date arithmetic across two timestamps in different tables. JOIN, AVG, ROUND. The carrier scorecard shape ops teams write weekly.
Need a January 2026 SLA cut by carrier — average hours between order_ts and the first 'shipped' event for web_ship orders. Three columns: carrier, avg_hours_to_ship. ROUND to 1 decimal. ORDER BY avg_hours_to_ship ASC (fastest carrier first), then carrier ASC for ties. Only event_type = 'shipped' (drop the split-ship 'in_transit' secondaries).
| Column | Type | Key |
|---|---|---|
| event_id | INT | PK |
| order_id | INT | FK → fact_orders |
| event_type | TEXT | |
| event_ts | TEXT | |
| carrier | TEXT | |
| zone | INT | |
| cost | REAL |
| Column | Type | Key |
|---|---|---|
| order_id | INT | PK |
| sku_id | INT | FK → dim_skus |
| store_id | INT | FK → dim_stores |
| channel | TEXT | |
| customer_zip | TEXT | |
| order_ts | TEXT | |
| units | INT | |
| gross_amount | REAL | |
| discount_amount | REAL | |
| fulfillment_cost | REAL |
Each hint you reveal reduces the XP you can earn. Try the query first.
JOIN fact_fulfillment_events to fact_orders on order_id.
Filter event_type = 'shipped' AND event_ts between 2026-01-01 and 2026-02-01 (half-open).
EXTRACT(EPOCH FROM (event_ts::timestamp - o.order_ts::timestamp)) / 3600 gives hours.
ROUND(AVG(...)::numeric, 1) for clean output.