ADB-weighted rate average across a date window
ALCO is meeting Friday. The Fed cut 50bp from the September peak to early November 2025 — I want our balance-weighted MMA deposit rate by month for Sep, Oct, and Nov 2025 so I can compute cumulative deposit beta. Pull from `retail_balance_daily` joined to `retail_accounts` and `retail_products`, MMA subcategory only. Three columns: `month` (the month number 9, 10, 11 as an integer), `weighted_avg_rate` (the ADB-weighted average of `interest_rate`, rounded to 6 decimals), and `avg_balance` (rounded to 2 decimals). Sort by month 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 |
|---|---|---|
| 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.
ADB-weighted rate isn't the same as a simple AVG. The formula is `SUM(rate * balance) / SUM(balance)` — bigger accounts pull the weighted-mean rate toward whatever they're earning.
Filter to MMA subcategory and the September–November 2025 window in WHERE. Use `EXTRACT(MONTH FROM snapshot_date::date)` to bucket by month — cast the TEXT date column first to avoid a type error.
Group on the month integer. Use the SUM-of-product-over-SUM pattern for the weighted rate (NULLIF the denominator so a zero-balance month doesn't divide-by-zero). Round to 6 decimals so basis-point shifts are visible. The third column is a plain AVG of the balance.