Count rows on a fact table with a WHERE filter and a GROUP BY. Practice reading the denormalized merchant_id on fact_substitutions so no join is needed yet.
Support tickets about swaps are climbing. I want substitution volume per merchant, but only the ones the customer actually approved (customer_approved = 1) since those are the real swaps. fact_substitutions carries merchant_id directly. Columns: merchant_id, substitution_count. ORDER BY substitution_count DESC, merchant_id ASC.
| 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.
fact_substitutions has its own merchant_id, so you can count without joining.
Filter with WHERE customer_approved = 1, then GROUP BY merchant_id.
Order by substitution_count descending with merchant_id as the tiebreaker so the worst offenders lead, and double-check the customer_approved = 1 filter is still in place or rejected swaps sneak into the count.