Savings Accounts Below the Minimum Average Balance

Hard
68.

Savings Accounts Below the Minimum Average Balance

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

ColumnType
account_idint
holder_namevarchar
opening_balanceint

Primary key: account_id.

One row per savings account; opening_balance is the balance at the start of 2024-03-01.

Table: AccountTxn

ColumnTypeValues
txn_idint
account_idint
txn_datedate
directionenumCR, DR
amountint

Primary key: txn_id.

One row per transaction from 2024-03-01 on; CR adds amount, DR subtracts it.

Example

The rows must come back in the order shown.

SavingsAccount

account_idholder_nameopening_balance
1Aditi Gupta12000
2Rohan Iyer2000
3Maria Bose10000
4Harsh Reddy25000

AccountTxn

txn_idaccount_idtxn_datedirectionamount
112024-03-02DR9000
212024-03-09CR15000
322024-03-01CR30000
422024-03-06DR28000
542024-03-05DR24000
642024-03-11CR50000
732024-03-12DR9000

Output

account_idholder_nameavg_daily_balance
1Aditi Gupta6900
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
account_idint
holder_namevarchar
opening_balanceint

Primary key: account_id.

One row per savings account; opening_balance is the balance at the start of 2024-03-01.

Table: AccountTxn

ColumnType
txn_idint
account_idint
txn_datedate
directionenum(CR, DR)
amountint

Primary key: txn_id.

One row per transaction from 2024-03-01 on; CR adds amount, DR subtracts it.

Examples

Example 1

SavingsAccount

account_idholder_nameopening_balance
1Aditi Gupta12000
2Rohan Iyer2000
3Maria Bose10000
4Harsh Reddy25000

AccountTxn

txn_idaccount_idtxn_datedirectionamount
112024-03-02DR9000
212024-03-09CR15000
322024-03-01CR30000
422024-03-06DR28000
542024-03-05DR24000
642024-03-11CR50000
732024-03-12DR9000

Output

account_idholder_nameavg_daily_balance
1Aditi Gupta6900

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_id

Another 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 →