Conditional aggregation: SUM(CASE WHEN ... THEN 1 ELSE 0 END). Ratio with NULLIF to guard against /0. The BOPIS scorecard metric every program manager owns.
Monthly QBR. Pull the BOPIS completion rate (pickups completed / total pickup attempts) bucketed monthly for Oct 2025 → Mar 2026. Four columns: month, total_pickups, completed_pickups, completion_pct. ROUND completion_pct to 2 decimals. Order by month ASC. Use no_show_flag = 0 as the 'completed' definition — that's what the dashboard team standardized on last quarter.
| Column | Type | Key |
|---|---|---|
| pickup_id | INT | PK |
| order_id | INT | FK → fact_orders |
| pickup_ts | TEXT | |
| pickup_window_minutes | INT | |
| no_show_flag | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Filter pickup_ts >= '2025-10-01' AND pickup_ts < '2026-04-01'.
Conditional aggregation: SUM(CASE WHEN no_show_flag = 0 THEN 1 ELSE 0 END) AS completed_pickups.
completion_pct = 100.0 * completed / NULLIF(total, 0), rounded to 2 decimals.
DATE_TRUNC to monthly buckets, GROUP BY month, ORDER BY month ASC.