Use HAVING to filter grouped results
Quick one for the quarterly deck — what's our repeat purchase rate? I'm writing the retention slide and I need one number: how many customers have made more than one purchase (refunds don't count). The CMO cares about the percentage, but if you give me the count I can compute the rest.
| 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.
Think about how to group purchases per customer, then keep only the customers with more than one. Refunds shouldn’t count as a purchase — so watch for negative amounts in `fact_purchases`.
That gives you the repeat buyers — but you need to COUNT them. Wrap it in a subquery: `SELECT COUNT(*) FROM (...) sub`
Alias the final result: `SELECT COUNT(*) AS repeat_customers FROM (...) sub` — the subquery needs an alias like `sub` after the closing parenthesis