CASE WHEN bucketing combined with GROUP BY for a categorical distribution
Before I can answer the CEO's question about portfolio mix, I need the FICO distribution of everyone who's ever applied. Bucket `fico_at_app` into `<600`, `600-639`, `640-679`, `680-719`, and `720+`. Show band name and application count. Sort by the FICO lower bound ascending — so `<600` is on top, `720+` at the bottom.
| Column | Type | Key |
|---|---|---|
| app_id | INT | PK |
| customer_id | INT | FK → banking_customers |
| product_type | TEXT | |
| app_date | TEXT | |
| decision | TEXT | |
| fico_at_app | INT | |
| offered_apr | REAL | |
| approved_limit | REAL | |
| booked_account_id | INT | FK → banking_accounts |
Each hint you reveal reduces the XP you can earn. Try the query first.
CASE WHEN with 5 thresholds, ELSE for the top band.
To sort by FICO lower bound without hardcoding, add `MIN(fico_at_app)` to ORDER BY — it works even though it's not in the SELECT list.
GROUP BY the band column, COUNT(*) for the app volume, and use MIN(fico_at_app) in the ORDER BY so the bands sort by their numeric lower bound rather than alphabetically.