Savings Accounts Below the Minimum Average Balance
Savings accounts must keep an average daily balance of ₹10,000 or pay a charge.
- Difficulty: Hard
- Topics: Dates, Joins, Aggregation
- Dialect: MySQL
- Problem: #68
Problem statement
Savings accounts must keep an average daily balance of ₹10,000 or pay a charge. For the review period 1–10 March 2024, an account's balance at the end of each day is its opening_balance (the balance at the start of 1 March) plus every transaction dated on or before that day; the average daily balance is the mean of those 10 end-of-day balances, including days with no transactions. Transactions after 10 March do not matter.
Return account_id, holder_name and avg_daily_balance (rounded to 2 decimals) for every account whose average daily balance is strictly below 10,000. Order the rows by avg_daily_balance, then by account_id.
Tables
Table: SavingsAccount
| Column | Type |
|---|---|
| account_id | int |
| holder_name | varchar |
| opening_balance | int |
Primary key: account_id.
One row per savings account; opening_balance is the balance at the start of 2024-03-01.
Table: AccountTxn
| Column | Type |
|---|---|
| txn_id | int |
| account_id | int |
| txn_date | date |
| direction | enum(CR, DR) |
| amount | int |
Primary key: txn_id.
One row per transaction from 2024-03-01 on; CR adds amount, DR subtracts it.
Examples
Example 1
SavingsAccount
| account_id | holder_name | opening_balance |
|---|---|---|
| 1 | Aditi Gupta | 12000 |
| 2 | Rohan Iyer | 2000 |
| 3 | Maria Bose | 10000 |
| 4 | Harsh Reddy | 25000 |
AccountTxn
| txn_id | account_id | txn_date | direction | amount |
|---|---|---|---|---|
| 1 | 1 | 2024-03-02 | DR | 9000 |
| 2 | 1 | 2024-03-09 | CR | 15000 |
| 3 | 2 | 2024-03-01 | CR | 30000 |
| 4 | 2 | 2024-03-06 | DR | 28000 |
| 5 | 4 | 2024-03-05 | DR | 24000 |
| 6 | 4 | 2024-03-11 | CR | 50000 |
| 7 | 3 | 2024-03-12 | DR | 9000 |
Output
| account_id | holder_name | avg_daily_balance |
|---|---|---|
| 1 | Aditi Gupta | 6900 |
How to solve Savings Accounts Below the Minimum Average Balance
The average is over days, not transactions, so the first job is a calendar. WITH RECURSIVE days starts at 2024-03-01 and adds a day until 2024-03-10, giving exactly ten rows. Cross-join it with the accounts to get one row per account per day, then LEFT JOIN each account's transactions dated on or before that day and sum their signed amounts; adding the opening balance gives the end-of-day balance. The LEFT JOIN and COALESCE keep a day before the first transaction, and an account with no transactions at all, at the opening balance.
Averaging the ten balances per account and keeping AVG(balance) < 10000 in HAVING finishes it; an account sitting at exactly 10,000 every day is not charged. Transactions after 10 March are excluded by the txn_date <= days.d condition, since no generated day is later.
The alternative avoids the calendar entirely with a little algebra: a transaction on day d is included in every end-of-day balance from d to 10 March, that is DATEDIFF('2024-03-10', d) + 1 of them, so the average is opening + Σ(signed amount × days remaining) / 10. That is one pass over the transactions instead of accounts × days × transactions, which is how a bank would compute it at scale — but the calendar version is the one that generalises to rules like "balance on every Sunday".
Reference solution (MySQL)
WITH RECURSIVE days AS (
SELECT CAST('2024-03-01' AS DATE) AS d
UNION ALL
SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM days WHERE d < '2024-03-10'
), eod AS (
SELECT a.account_id, a.holder_name, days.d,
a.opening_balance + COALESCE(SUM(CASE WHEN t.direction = 'CR' THEN t.amount ELSE -t.amount END), 0) AS balance
FROM SavingsAccount a
CROSS JOIN days
LEFT JOIN AccountTxn t ON t.account_id = a.account_id AND t.txn_date <= days.d
GROUP BY a.account_id, a.holder_name, a.opening_balance, days.d
)
SELECT account_id, holder_name, ROUND(AVG(balance), 2) AS avg_daily_balance
FROM eod
GROUP BY account_id, holder_name
HAVING AVG(balance) < 10000
ORDER BY avg_daily_balance, account_idAnother way
SELECT account_id, holder_name, ROUND(avg_balance, 2) AS avg_daily_balance FROM (
SELECT a.account_id, a.holder_name,
a.opening_balance + COALESCE(SUM(CASE WHEN t.direction = 'CR' THEN t.amount ELSE -t.amount END * (DATEDIFF('2024-03-10', t.txn_date) + 1)), 0) / 10 AS avg_balance
FROM SavingsAccount a
LEFT JOIN AccountTxn t ON t.account_id = a.account_id AND t.txn_date <= '2024-03-10'
GROUP BY a.account_id, a.holder_name, a.opening_balance
) x WHERE avg_balance < 10000 ORDER BY avg_daily_balance, account_id← Longest Run of Consecutive Missed Loan Instalments · Median Card Spend by Merchant Category →