AVG over a daily-balance snapshot window
Monthly maintenance fees are about to assess. The waiver threshold on Total Checking is $1,500 ADB for the calendar month — I need the list of accounts that fell short in October 2025 so the call center can outreach before the fee posts. From `retail_balance_daily`, compute average ledger balance per account for snapshot dates from 2025-10-01 through 2025-10-31. Return `account_id` and `adb_oct` (rounded to 2 decimals) for accounts where the ADB is strictly below $1,500. Sort ADB 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
Daily balance is one row per (account, date). The 'average daily balance' is just AVG(ledger_balance) grouped by account, with the date window filtered in WHERE.
Filter `snapshot_date BETWEEN '2025-10-01' AND '2025-10-31'` before the GROUP BY so the AVG only reflects October. Round the result to 2 decimals to match the dollar-display convention.
After the GROUP BY, gate on the threshold with HAVING (the average is an aggregate). Strict less-than against the briefing's $1,500 waiver threshold keeps only the accounts that fell short.