Compute a rate with conditional aggregation (AVG of a CASE) and handle the NULL-shopper rows that represent undispatched orders.
Quarterly shopper reviews. I need a completion rate per shopper: of the orders assigned to them, what share ended up delivered? Use fact_orders.order_status. Ignore orders with no shopper assigned (shopper_id IS NULL). Columns: shopper_id, completion_rate (4 decimals). ORDER BY completion_rate DESC, shopper_id ASC.
| Column | Type | Key |
|---|---|---|
| order_id | INT | PK |
| customer_id | INT | FK → dim_customers |
| merchant_id | INT | FK → dim_merchants |
| shopper_id | INT | FK → dim_shoppers |
| dispatch_id | INT | FK → fact_shopper_dispatches |
| order_ts | TEXT | |
| order_status | TEXT | |
| units | INT | |
| item_subtotal | REAL | |
| delivery_fee | REAL | |
| service_fee | REAL | |
| surge_fee | REAL | |
| surge_multiplier | REAL | |
| tip_amount | REAL | |
| customer_total | REAL | |
| shopper_take | REAL | |
| platform_take | REAL | |
| merchant_payout | REAL |
Each hint you reveal reduces the XP you can earn. Try the query first.
An order counts as completed when order_status = 'delivered'.
A delivered/not-delivered flag (1 or 0) averaged across a shopper's orders is their completion rate: reach for AVG over a CASE expression, and make the 'yes' value 1.0 so you don't fall into integer division.
Filter WHERE shopper_id IS NOT NULL, GROUP BY shopper_id, ROUND to 4 decimals.