Combine CASE WHEN with multi-table JOINs and multiple aggregation levels
For the revenue-concentration review, classify every sale (grain: one row per sale) by deal size: 'Small' if revenue < 100, 'Medium' for 100–500, 'Large' for 500–2000, 'Enterprise' over 2000. Show the bucket, number of transactions, total revenue, and average revenue per deal. Sort by total revenue descending. I want to see how concentrated our revenue is — if Enterprise deals carry 40%+ of revenue on 5% of transactions, we have real customer-concentration risk and the board will ask about it.
| 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.
Use CASE WHEN on the revenue column to create deal size buckets
GROUP BY the CASE WHEN expression and compute COUNT(*), SUM(revenue), AVG(revenue)
Use ROUND on the average for cleaner output