Inoperative Accounts Under the Two-Year Rule — SQL Problem
An account becomes inoperative when its holder has made no customer-induced transaction for two years.
- Difficulty: Medium
- Topics: Subqueries, Dates
- Dialect: MySQL
- Problem: #63
Problem statement
An account becomes inoperative when its holder has made no customer-induced transaction for two years. Interest credits are posted by the bank and do not count as activity. On the review date 2025-06-30, an account is inoperative when it was opened on or before 2023-06-30 and has no transaction other than INTEREST dated after 2023-06-30.
Return account_id, holder_name and last_customer_txn — the date of the account's latest non-interest transaction, or NULL if it never had one — for every inoperative account, ordered by account_id.
Tables
Table: Account
| Column | Type |
|---|---|
| account_id | int |
| holder_name | varchar |
| opened_on | date |
Primary key: account_id.
One row per savings account.
Table: AccountActivity
| Column | Type |
|---|---|
| txn_id | int |
| account_id | int |
| txn_date | date |
| channel | enum(ATM, UPI, BRANCH, NETBANKING, INTEREST) |
Primary key: txn_id.
One row per transaction; channel INTEREST marks the bank's own interest credit.
Examples
Example 1
Account
| account_id | holder_name | opened_on |
|---|---|---|
| 1 | Aarav Nair | 2019-04-10 |
| 2 | Kavya Rao | 2020-08-01 |
| 3 | John Mehta | 2023-06-30 |
| 4 | Riya Sharma | 2024-01-15 |
| 5 | Dev Khan | 2021-11-20 |
AccountActivity
| txn_id | account_id | txn_date | channel |
|---|---|---|---|
| 1 | 1 | 2022-12-05 | ATM |
| 2 | 1 | 2024-03-31 | INTEREST |
| 3 | 2 | 2023-07-01 | UPI |
| 4 | 2 | 2021-02-14 | BRANCH |
| 5 | 4 | 2024-09-30 | INTEREST |
| 6 | 5 | 2023-06-30 | NETBANKING |
| 7 | 5 | 2025-03-31 | INTEREST |
Output
| account_id | holder_name | last_customer_txn |
|---|---|---|
| 1 | Aarav Nair | 2022-12-05 |
| 3 | John Mehta | NULL |
| 5 | Dev Khan | 2023-06-30 |
How to solve Inoperative Accounts Under the Two-Year Rule
Two conditions define an inoperative account: it is at least two years old (opened_on <= '2023-06-30'), and no customer-induced transaction is dated after 2023-06-30. The second is a natural NOT EXISTS with a correlated subquery that ignores INTEREST rows. The reported date is a separate correlated MAX over the same non-interest transactions, NULL when there are none.
Equivalently, compute each account's latest customer transaction and keep the accounts where it is NULL or on/before the cut-off; that is the grouped LEFT JOIN alternative. The NULL arm is essential — MAX(...) <= '2023-06-30' alone is unknown for an account that never transacted, and the most dormant accounts of all would vanish. The interest filter belongs in the join condition, for the same reason as in any outer join.
Boundary dates matter here: a transaction on 2023-06-30 is not after the cut-off, one on 2023-07-01 is, and an account opened exactly on 2023-06-30 is old enough. Each version is one pass over the accounts with an index lookup on activity per account.
Reference solution (MySQL)
SELECT a.account_id, a.holder_name,
(SELECT MAX(t.txn_date) FROM AccountActivity t
WHERE t.account_id = a.account_id AND t.channel <> 'INTEREST') AS last_customer_txn
FROM Account a
WHERE a.opened_on <= '2023-06-30'
AND NOT EXISTS (SELECT 1 FROM AccountActivity t
WHERE t.account_id = a.account_id AND t.channel <> 'INTEREST' AND t.txn_date > '2023-06-30')
ORDER BY a.account_idAnother way
SELECT a.account_id, a.holder_name, MAX(t.txn_date) AS last_customer_txn
FROM Account a LEFT JOIN AccountActivity t ON t.account_id = a.account_id AND t.channel <> 'INTEREST'
WHERE a.opened_on <= '2023-06-30'
GROUP BY a.account_id, a.holder_name
HAVING MAX(t.txn_date) IS NULL OR MAX(t.txn_date) <= '2023-06-30'
ORDER BY a.account_idAnother way
SELECT account_id, holder_name, last_customer_txn FROM (
SELECT a.account_id, a.holder_name, a.opened_on,
(SELECT MAX(t.txn_date) FROM AccountActivity t WHERE t.account_id = a.account_id AND t.channel IN ('ATM', 'UPI', 'BRANCH', 'NETBANKING')) AS last_customer_txn
FROM Account a
) x WHERE DATEDIFF('2023-06-30', opened_on) >= 0 AND COALESCE(last_customer_txn, '1900-01-01') <= '2023-06-30'
ORDER BY account_id← Branch Deposits Against the Previous Calendar Month · Loan Book Asset Classification by Branch →