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
| Column | Type |
|---|---|
| account_id | int |
| holder_name | varchar |
| opened_on | date |
Primary key: account_id.
One row per account; the balance is 0 on opened_on.
Table: NeoTxn
| Column | Type |
|---|---|
| txn_id | int |
| account_id | int |
| txn_date | date |
| txn_type | enum(CREDIT, DEBIT) |
| amount | int |
Primary key: txn_id.
One row per transaction, never dated before the account's opened_on.
Examples
Example 1
NeoAccount
| account_id | holder_name | opened_on |
|---|---|---|
| 1 | Ananya Verma | 2024-01-10 |
| 2 | Kabir Das | 2024-01-15 |
| 3 | Noah Iyer | 2024-02-01 |
| 4 | Simran Khan | 2024-02-05 |
NeoTxn
| txn_id | account_id | txn_date | txn_type | amount |
|---|---|---|---|---|
| 1 | 1 | 2024-01-10 | CREDIT | 60000 |
| 2 | 1 | 2024-01-31 | CREDIT | 45000 |
| 3 | 1 | 2024-02-02 | DEBIT | 30000 |
| 4 | 2 | 2024-01-20 | CREDIT | 99000 |
| 5 | 2 | 2024-01-25 | DEBIT | 5000 |
| 6 | 2 | 2024-03-01 | CREDIT | 6000 |
| 7 | 3 | 2024-02-01 | CREDIT | 100000 |
| 8 | 4 | 2024-02-10 | CREDIT | 80000 |
| 9 | 4 | 2024-02-10 | DEBIT | 20000 |
| 10 | 4 | 2024-02-10 | CREDIT | 40000 |
Output
| account_id | holder_name | reached_on | days_to_lakh |
|---|---|---|---|
| 3 | Noah Iyer | 2024-02-01 | 0 |
| 4 | Simran Khan | 2024-02-10 | 5 |
| 1 | Ananya Verma | 2024-01-31 | 21 |
| 2 | Kabir Das | 2024-03-01 | 46 |
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_idAnother 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 →