Conditional COUNT pivot + multi-CTE assembly
Build the per-customer Customer 360 row for the top 5 depositors (Active customers only, by total deposit balance). Latest snapshot per account. Eight columns: `customer_id`, `household_id`, `account_count` (open accounts only), `n_checking`, `n_savings` (treat 'savings' miscased as Savings), `n_cards`, `n_loans`, `total_deposits` (rounded 2 decimals). Sort by total_deposits descending, then customer_id ascending. LIMIT 5.
| 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 |
|---|---|---|
| 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 |
| 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
Pre-aggregate the latest balance per account in a CTE (ROW_NUMBER PARTITION BY account ORDER BY snapshot_date DESC, keep rn=1). Then LEFT JOIN that to `retail_accounts` filtered to status='Open'.
Conditional COUNT pivots — COUNT DISTINCT account_id wrapped in a CASE that filters to the right subcategory. Watch the savings miscasing trap: LOWER on subcategory collapses 'Savings' and 'savings' into one count.
Outer SELECT groups by customer_id and household_id. ORDER BY total_deposits descending with customer_id ascending as the tiebreaker, then LIMIT to the top depositors the briefing asked for.