Turn two separate counts into a ratio per merchant, guarding the denominator with NULLIF and keeping every merchant via a LEFT JOIN. The first two-table aggregation of the path.
Customers are grumbling about swaps and I need to know who to coach. Give me substitution rate per merchant: substitutions divided by orders. Keep all 80 merchants even if a merchant somehow has zero subs, and don't blow up on any merchant with zero orders. Columns: merchant_id, substitution_rate (rounded to 4 decimals). ORDER BY substitution_rate DESC, merchant_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 |
| Column | Type | Key |
|---|---|---|
| substitution_id | INT | PK |
| order_id | INT | FK → fact_orders |
| merchant_id | INT | FK → dim_merchants |
| shopper_id | INT | FK → dim_shoppers |
| sub_ts | TEXT | |
| original_item_price | REAL | |
| sub_item_price | REAL | |
| customer_charged | REAL | |
| merchant_paid | REAL | |
| reason | TEXT | |
| customer_approved | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
You need two per-merchant counts: orders from fact_orders and substitutions from fact_substitutions.
Aggregate each table to merchant grain in its own CTE, then LEFT JOIN substitutions onto orders so every merchant survives.
Divide subs by orders, but guard both ends: COALESCE the substitution count so a no-sub merchant reads 0 instead of NULL, and wrap the denominator in NULLIF so a zero-order merchant can't divide by zero. Then round to 4 decimals.