Daily-balance × rate aggregation across two dimensions
ALCO follow-up. Build the input table for the NII decomposition: monthly interest income (or expense) by product category, Sep through Nov 2025. From `retail_balance_daily` joined to `retail_accounts` and `retail_products`, compute daily interest as `ledger_balance * interest_rate / 365.0`, sum it up per (month, category). Three columns: `month` (the integer 9, 10, 11), `category` (Deposit / Loan / Card), `interest_dollars` (rounded to 2 decimals). Sort by month, then category. Skip rows with NULL interest_rate.
| 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.
Daily interest accrual is `balance * rate / 365` — one number per (account, day). The monthly total per category is the SUM across all those daily rows after filtering.
Bucket by month with EXTRACT on the cast snapshot_date. Filter the briefing's date window in WHERE first so the SUM only includes the right months, and exclude rows where interest_rate is NULL (debit cards and some products carry no rate).
Two-dimensional GROUP BY — month and category. ROUND the dollar column to 2 decimals. ORDER BY month ascending, then category ascending.