Use CASE WHEN to bucket continuous values into categories and aggregate by bucket
Finance thinks we're leaving revenue on the table with our discount strategy — specifically, they suspect 'High' discount sales aren't pulling their weight. Bucket all sales (grain: one row per sale) into discount tiers — 'No Discount' for 0 or NULL, 'Low' up to 10%, 'Medium' 10–20%, 'High' over 20%. Give me the tier name, number of sales, and total revenue per tier, sorted by revenue descending. If 'High' discount drives less revenue than 'Medium' despite bigger giveaways, that's a conversation I want to have with finance before Monday.
| Column | Type | Key |
|---|---|---|
| sale_id | INT | PK |
| employee_id | INT | FK → dim_employees |
| product_id | INT | FK → dim_products |
| region_id | INT | FK → dim_regions |
| sale_date | TEXT | |
| quantity | INT | |
| revenue | REAL | |
| discount | REAL |
Each hint you reveal reduces the XP you can earn. Try the query first.
Discounts have three real-world states — not given, given but tiny, given and material. Think about which of those NULL represents here and make sure it doesn't fall through to the wrong bucket.
Remember that NULL discount and 0 discount are different values but belong in the same business tier here
GROUP BY the CASE WHEN expression (or alias) to aggregate per tier