Latest-snapshot range filtering for behavioral targeting
We're modeling a tiered-rate offer to lift balances over the $25K break. Pull the candidate list — Savings or MMA accounts at the latest snapshot whose ledger_balance is below $25K but at least $20K (so a small top-up gets them over). Output `account_id`, `customer_id`, `subcategory`, `current_balance` (rounded 2), `dist_to_25k_break` (25000 − current, rounded 2). Treat the miscased 'savings' subcategory as a Savings entry. Sort by dist_to_25k_break ascending so the closest-to-the-line accounts surface first.
| 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 is the same `ROW_NUMBER PARTITION BY account ORDER BY snapshot_date DESC` pattern you used in M19 / M21. Keep `rn = 1`.
Filter the subcategory IN ('MMA', 'Savings', 'savings') — the lowercase row is a known data bug from M2 / M3 which still needs to be included here.
Range filter on the latest balance using the bounds in the briefing. The upper bound is half-open — at exactly $25K the account already qualifies for the next tier, so it's not a candidate.