Conditional aggregation across product categories with latest-snapshot ROW_NUMBER
Quarterly board pack. I need Loan-to-Deposit ratio by region — using the latest balance snapshot per account. From `retail_balance_daily`, pick `rn=1` per account (ROW_NUMBER PARTITION BY account ORDER BY snapshot_date DESC). Join to `retail_accounts` → `retail_branches` for region and `retail_products` for category. Pivot Loan vs Deposit balances per region with conditional SUM. Five columns: `region`, `deposit_balance`, `loan_balance`, `ldr_pct` (loan ÷ deposit × 100, rounded to 1 decimal). Sort by region 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 |
|---|---|---|
| branch_id | INT | PK |
| branch_name | TEXT | |
| region | TEXT | |
| state | TEXT | |
| lmi_tract_flag | INT | |
| open_date | TEXT | |
| close_date | TEXT | |
| branch_type | 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: ROW_NUMBER PARTITION BY account ORDER BY snapshot_date DESC, then keep only the rn=1 row. Collapses many daily rows down to one current balance per account.
Pivot Deposit vs Loan into separate columns with conditional SUM (CASE inside the SUM). Round the dollar columns to 2 decimals.
LDR is loans divided by deposits, multiplied by 100, rounded to 1. NULLIF the denominator so a region with zero deposits doesn't crash the division. Card category isn't in scope here.