Conditional bucketing with multi-condition CASE + FDIC limit logic
FRB stress prep. Bucket every Deposit account by FDIC insurance status using the latest balance snapshot per account. Buckets: `insured_individual` (Individual ownership, balance ≤ $250K), `uninsured_individual` (Individual, balance > $250K), `insured_joint` (Joint, balance ≤ $500K), `uninsured_joint` (Joint, balance > $500K). Three columns: `bucket`, `n_accounts`, `balance` (rounded 2). Sort by bucket ascending.
| Column | Type | Key |
|---|---|---|
| snapshot_id | INT | PK |
| account_id | INT | FK → retail_accounts |
| snapshot_date | TEXT | |
| ledger_balance | REAL | |
| available_balance | REAL | |
| interest_rate | REAL | |
| days_overdrawn | INT | |
| delinquency_status | TEXT |
| 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.
Latest snapshot per account using the ROW_NUMBER pattern — keep the most recent. Filter to the Deposit category; loans aren't in FDIC scope.
The bucket CASE has multiple branches. Order matters: check Joint+over-limit BEFORE the plain Joint branch, otherwise every joint account lands in the insured bucket regardless of balance.
GROUP BY the bucket label, COUNT for accounts, SUM the balance for dollar exposure, ROUND to 2.