Histogram bucketing with CASE WHEN
Product team is reviewing the MMA tiering. I want a balance histogram for our open MMA accounts using these breaks: under $10K, $10K to under $25K, $25K to under $50K, $50K and up. From `retail_accounts` joined to `retail_products`, filter `subcategory = 'MMA'` and `status = 'Open'`. Two columns: `tier` (the bucket label, prefixed `1_` / `2_` / `3_` / `4_` so it sorts cleanly) and `n_accounts`. Sort by tier ascending. Tiers with zero accounts can be omitted — I just want what's actually in the book.
| Column | Type | Key |
|---|---|---|
| account_id | INT | PK |
| customer_id | INT | FK → retail_customers |
| product_id | INT | FK → retail_products |
| branch_id | INT | FK → retail_branches |
| open_date | TEXT | |
| close_date | TEXT | |
| status | TEXT | |
| ownership_type | TEXT | |
| interest_rate | REAL | |
| original_balance | REAL | |
| credit_limit | REAL | |
| fico_at_orig | INT | |
| maturity_date | TEXT |
| Column | Type | Key |
|---|---|---|
| product_id | INT | PK |
| product_code | TEXT | |
| product_name | TEXT | |
| category | TEXT | |
| subcategory | TEXT | |
| default_apr | REAL | |
| default_apy | REAL | |
| monthly_fee | REAL | |
| nsf_fee | REAL | |
| od_fee | REAL |
Each hint you reveal reduces the XP you can earn. Try the query first.
Three CASE branches plus an ELSE for the top bucket. Label each tier with a leading digit (`'1_under_10k'`, `'2_10k_to_25k'`, ...) so ORDER BY puts them in numeric order rather than alphabetical.
Filter the join: `subcategory = 'MMA'` AND `status = 'Open'`. Anything else (other deposit products, closed accounts) shouldn't be in the rollup.
GROUP BY the tier expression, COUNT(*) for the count, ORDER BY the tier ascending. Tiers with zero accounts simply don't appear — that's fine for this mission.