Days From Account Opening to a ₹1 Lakh Balance

Hard
72.

Days From Account Opening to a ₹1 Lakh Balance

A neobank measures how quickly new customers start using it as their main account: the number of days from opening until the balance first reaches ₹1,00,000 or more. Every account opens at zero; credits add to the balance, debits subtract, and transactions are applied in order of txn_date, then txn_id. A balance that reaches the mark and later falls back still counts from the first time.

Return account_id, holder_name, reached_on (the txn_date of the transaction that first brought the balance to 1,00,000 or more) and days_to_lakh (days from opened_on to reached_on) for every account that ever reached it. Order the rows by days_to_lakh, then by account_id.

Tables

Table: NeoAccount

ColumnType
account_idint
holder_namevarchar
opened_ondate

Primary key: account_id.

One row per account; the balance is 0 on opened_on.

Table: NeoTxn

ColumnTypeValues
txn_idint
account_idint
txn_datedate
txn_typeenumCREDIT, DEBIT
amountint

Primary key: txn_id.

One row per transaction, never dated before the account's opened_on.

Example

The rows must come back in the order shown.

NeoAccount

account_idholder_nameopened_on
1Ananya Verma2024-01-10
2Kabir Das2024-01-15
3Noah Iyer2024-02-01
4Simran Khan2024-02-05

NeoTxn

txn_idaccount_idtxn_datetxn_typeamount
112024-01-10CREDIT60000
212024-01-31CREDIT45000
312024-02-02DEBIT30000
422024-01-20CREDIT99000
522024-01-25DEBIT5000
622024-03-01CREDIT6000
732024-02-01CREDIT100000
842024-02-10CREDIT80000
942024-02-10DEBIT20000
1042024-02-10CREDIT40000

Output

account_idholder_namereached_ondays_to_lakh
3Noah Iyer2024-02-010
4Simran Khan2024-02-105
1Ananya Verma2024-01-3121
2Kabir Das2024-03-0146
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.

Days From Account Opening to a ₹1 Lakh Balance — SQL Problem

A neobank measures how quickly new customers start using it as their main account: the number of days from opening until the balance first reaches ₹1,00,000…

  • Difficulty: Hard
  • Topics: Window Functions, Dates, Joins
  • Dialect: MySQL
  • Problem: #72

Problem statement

A neobank measures how quickly new customers start using it as their main account: the number of days from opening until the balance first reaches ₹1,00,000 or more. Every account opens at zero; credits add to the balance, debits subtract, and transactions are applied in order of txn_date, then txn_id. A balance that reaches the mark and later falls back still counts from the first time.

Return account_id, holder_name, reached_on (the txn_date of the transaction that first brought the balance to 1,00,000 or more) and days_to_lakh (days from opened_on to reached_on) for every account that ever reached it. Order the rows by days_to_lakh, then by account_id.

Tables

Table: NeoAccount

ColumnType
account_idint
holder_namevarchar
opened_ondate

Primary key: account_id.

One row per account; the balance is 0 on opened_on.

Table: NeoTxn

ColumnType
txn_idint
account_idint
txn_datedate
txn_typeenum(CREDIT, DEBIT)
amountint

Primary key: txn_id.

One row per transaction, never dated before the account's opened_on.

Examples

Example 1

NeoAccount

account_idholder_nameopened_on
1Ananya Verma2024-01-10
2Kabir Das2024-01-15
3Noah Iyer2024-02-01
4Simran Khan2024-02-05

NeoTxn

txn_idaccount_idtxn_datetxn_typeamount
112024-01-10CREDIT60000
212024-01-31CREDIT45000
312024-02-02DEBIT30000
422024-01-20CREDIT99000
522024-01-25DEBIT5000
622024-03-01CREDIT6000
732024-02-01CREDIT100000
842024-02-10CREDIT80000
942024-02-10DEBIT20000
1042024-02-10CREDIT40000

Output

account_idholder_namereached_ondays_to_lakh
3Noah Iyer2024-02-010
4Simran Khan2024-02-105
1Ananya Verma2024-01-3121
2Kabir Das2024-03-0146

How to solve Days From Account Opening to a ₹1 Lakh Balance

The balance after each transaction is a running total of signed amounts — SUM(CASE WHEN txn_type = 'CREDIT' THEN amount ELSE -amount END) OVER (PARTITION BY account_id ORDER BY txn_date, txn_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Ordering by txn_id within a date applies same-day transactions one at a time, so a balance that touches 1,00,000 in the middle of a busy day counts (the example's fourth account reaches it on its third transaction of 10 February).

The first time the mark is reached is the earliest row with balance >= 100000; because rows are ordered by date first, MIN(txn_date) over those rows is that transaction's date. Later rows below the mark change nothing — the question is about the first crossing, not the current balance. Accounts that never reach it have no qualifying row and drop out of the inner join. DATEDIFF(reached_on, opened_on) gives the days, 0 when it happens on the opening day.

Without window functions, a correlated subquery recomputes the running balance for every transaction with the same date-then-id tie rule; the grouped MIN is the same. That is quadratic per account, against one sort for the window version.

Reference solution (MySQL)

WITH running AS (
  SELECT account_id, txn_date,
         SUM(CASE WHEN txn_type = 'CREDIT' THEN amount ELSE -amount END)
           OVER (PARTITION BY account_id ORDER BY txn_date, txn_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance
  FROM NeoTxn
), first_hit AS (
  SELECT account_id, MIN(txn_date) AS reached_on
  FROM running
  WHERE balance >= 100000
  GROUP BY account_id
)
SELECT a.account_id, a.holder_name, f.reached_on, DATEDIFF(f.reached_on, a.opened_on) AS days_to_lakh
FROM first_hit f
JOIN NeoAccount a ON a.account_id = f.account_id
ORDER BY days_to_lakh, a.account_id

Another way

SELECT a.account_id, a.holder_name, MIN(t.txn_date) AS reached_on, DATEDIFF(MIN(t.txn_date), a.opened_on) AS days_to_lakh
FROM NeoAccount a JOIN NeoTxn t ON t.account_id = a.account_id
WHERE (SELECT SUM(IF(u.txn_type = 'CREDIT', u.amount, -u.amount)) FROM NeoTxn u
       WHERE u.account_id = t.account_id AND (u.txn_date < t.txn_date OR (u.txn_date = t.txn_date AND u.txn_id <= t.txn_id))) >= 100000
GROUP BY a.account_id, a.holder_name, a.opened_on
ORDER BY days_to_lakh, a.account_id

← Top Two PSP Handles by Monthly UPI Volume · Library Loans Overdue on Audit Day →