CASE WHEN to bucket a numeric column into categorical ranges
I need the portfolio aging as of the latest `banking_delinquency` snapshot. Bucket `days_past_due` into Current (=0), 1-29, 30-59, 60-89, 90-119, and 120+. Show bucket name and snapshot count. Only rows from the most recent `snapshot_date` — not the full history.
| Column | Type | Key |
|---|---|---|
| snapshot_id | INT | PK |
| account_id | INT | FK → banking_accounts |
| snapshot_date | TEXT | |
| days_past_due | INT | |
| balance | REAL | |
| is_charged_off | INT | |
| recoveries | REAL |
Each hint you reveal reduces the XP you can earn. Try the query first.
This table has multiple monthly snapshots — you only want the most recent one. Think about how to identify and filter to the latest date before you start bucketing.
Use CASE WHEN to build the bucket column. Order the WHEN clauses from tightest to loosest — SQL stops at the first match.
In the CASE WHEN, order branches from tightest band to widest — SQL stops at the first match, so '< 30' must come before '< 60'. Filter snapshot_date to the most recent date before bucketing, then GROUP BY the bucket column and ORDER BY it.