Latest-snapshot pivot + multi-table customer attribute join
CFO wants to see the deposit book split by how the customer was acquired. Use the latest balance snapshot per account (ROW_NUMBER PARTITION BY account ORDER BY snapshot_date DESC, keep rn=1). Join through customers for `acquisition_channel`. Filter to Deposit category only. Three columns: `acquisition_channel`, `n_accounts` (distinct count), `deposit_balance` (rounded 2 decimals). Sort by acquisition_channel 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 |
|---|---|---|
| customer_id | INT | PK |
| household_id | TEXT | |
| first_name | TEXT | |
| last_name | TEXT | |
| date_of_birth | TEXT | |
| state | TEXT | |
| segment | TEXT | |
| fico_current | INT | |
| estimated_income | REAL | |
| kyc_risk | TEXT | |
| acquisition_channel | TEXT | |
| onboarded_date | TEXT | |
| status | 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.
Same latest-snapshot CTE pattern as M19: ROW_NUMBER PARTITION BY account_id ORDER BY snapshot_date DESC, then keep `rn = 1`. Collapses 90 daily rows down to one per account.
Walk: `latest` → `retail_accounts` → `retail_customers` (for acquisition_channel) → `retail_products` (for category filter). Filter `category = 'Deposit'` so loans and cards don't slip in.
GROUP BY acquisition_channel. COUNT DISTINCT account_id (defensive against join fanout), SUM the latest balance, ROUND to 2. Sort ascending by channel.