Use GROUP BY with WHERE to filter and aggregate coupon performance
Fyi finance is asking about our coupon codes — can you show me each coupon code, how many times it was used, and the total revenue it drove? No rush, sometime today. Skip the refund rows — they want gross from the redemption side.
| Column | Type | Key |
|---|---|---|
| purchase_id | INT | PK |
| customer_id | INT | FK → dim_customers |
| purchase_date | TEXT | |
| amount | REAL | |
| product_category | TEXT | |
| campaign_id | INT | FK → dim_campaigns |
| coupon_code | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two filters matter here: only rows where a coupon was actually used, and only revenue-positive rows. Remember the briefing wanted zero-dollar trial coupons counted too — that affects how you write the amount filter.
Group by `coupon_code` and compute `COUNT(*) AS times_used` and `SUM(amount) AS total_revenue`
Three columns: coupon code, redemption count, total revenue. Round the revenue, alias it, sort descending so the top performers land first.