Inoperative Accounts Under the Two-Year Rule

Medium
63.

Inoperative Accounts Under the Two-Year Rule

MediumSubqueriesDates

MySQL

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

ColumnType
account_idint
holder_namevarchar
opened_ondate

Primary key: account_id.

One row per savings account.

Table: AccountActivity

ColumnTypeValues
txn_idint
account_idint
txn_datedate
channelenumATM, UPI, BRANCH, NETBANKING, INTEREST

Primary key: txn_id.

One row per transaction; channel INTEREST marks the bank's own interest credit.

Example

The rows must come back in the order shown.

Account

account_idholder_nameopened_on
1Aarav Nair2019-04-10
2Kavya Rao2020-08-01
3John Mehta2023-06-30
4Riya Sharma2024-01-15
5Dev Khan2021-11-20

AccountActivity

txn_idaccount_idtxn_datechannel
112022-12-05ATM
212024-03-31INTEREST
322023-07-01UPI
422021-02-14BRANCH
542024-09-30INTEREST
652023-06-30NETBANKING
752025-03-31INTEREST

Output

account_idholder_namelast_customer_txn
1Aarav Nair2022-12-05
3John MehtaNULL
5Dev Khan2023-06-30
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.

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

ColumnType
account_idint
holder_namevarchar
opened_ondate

Primary key: account_id.

One row per savings account.

Table: AccountActivity

ColumnType
txn_idint
account_idint
txn_datedate
channelenum(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_idholder_nameopened_on
1Aarav Nair2019-04-10
2Kavya Rao2020-08-01
3John Mehta2023-06-30
4Riya Sharma2024-01-15
5Dev Khan2021-11-20

AccountActivity

txn_idaccount_idtxn_datechannel
112022-12-05ATM
212024-03-31INTEREST
322023-07-01UPI
422021-02-14BRANCH
542024-09-30INTEREST
652023-06-30NETBANKING
752025-03-31INTEREST

Output

account_idholder_namelast_customer_txn
1Aarav Nair2022-12-05
3John MehtaNULL
5Dev Khan2023-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_id

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

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