PhonePe: Bank Downtime Runs From Consecutive Technical Declines
A business decline (wrong PIN, low balance) is the user's problem; a technical decline means the bank's systems did not answer.
- Difficulty: Hard
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #893
Problem statement
A business decline (wrong PIN, low balance) is the user's problem; a technical decline means the bank's systems did not answer. Several technical declines in a row at one bank usually mean the bank is down, and the payments-reliability team wants those runs listed.
Take each bank's transactions in txn_at order. A run is a stretch of three or more consecutive technical_decline transactions at the same bank with nothing else between them. Return bank_code, run_start and run_end (the first and last txn_at of the run) and declines, the number of transactions in it. A bank never has two transactions at the same moment. Order the rows by bank_code, then run_start.
A practice scenario set at PhonePe: the tables and data are invented, and this is not a question from, or endorsed by, PhonePe.
Tables
Table: BankTxn
| Column | Type |
|---|---|
| txn_id | int |
| bank_code | varchar |
| txn_at | datetime |
| status | enum(success, business_decline, technical_decline) |
Primary key: txn_id.
One row per UPI transaction routed to the bank.
Examples
Example 1
BankTxn
| txn_id | bank_code | txn_at | status |
|---|---|---|---|
| 1 | SBI | 2025-10-03 10:00:00 | success |
| 2 | SBI | 2025-10-03 10:01:00 | technical_decline |
| 3 | SBI | 2025-10-03 10:02:00 | technical_decline |
| 4 | SBI | 2025-10-03 10:03:00 | technical_decline |
| 5 | SBI | 2025-10-03 10:04:00 | business_decline |
| 6 | SBI | 2025-10-03 10:05:00 | technical_decline |
| 7 | SBI | 2025-10-03 10:06:00 | technical_decline |
| 8 | SBI | 2025-10-03 10:07:00 | success |
| 9 | HDFC | 2025-10-03 11:00:00 | success |
| 10 | HDFC | 2025-10-03 11:02:00 | technical_decline |
| 11 | HDFC | 2025-10-03 11:03:00 | technical_decline |
| 12 | ICICI | 2025-10-03 11:04:00 | technical_decline |
| 13 | HDFC | 2025-10-03 11:05:00 | technical_decline |
| 14 | HDFC | 2025-10-03 11:09:00 | technical_decline |
Output
| bank_code | run_start | run_end | declines |
|---|---|---|---|
| HDFC | 2025-10-03 11:02:00 | 2025-10-03 11:09:00 | 4 |
| SBI | 2025-10-03 10:01:00 | 2025-10-03 10:03:00 | 3 |
How to solve PhonePe: Bank Downtime Runs From Consecutive Technical Declines
Consecutive rows with the same property are an islands problem, and the trick is a key that is constant inside an island and changes between islands. Over each bank's transactions in time order, a running count of the rows that are not technical declines does exactly that: it stays fixed across a run of technical declines and steps up each time something else interrupts it. Every run of declines at a bank therefore shares one (bank_code, grp) pair, and no two runs share one.
Keeping only the technical declines and grouping by that pair gives each run's first time, last time and length; HAVING COUNT(*) >= 3 drops the short blips. In the example SBI's 10:05–10:06 pair is too short, and HDFC's 11:02–11:09 run counts four rows even though an ICICI transaction sits between them in time — runs are per bank, so the partition ignores other banks entirely.
The first alternative is the classic difference of row numbers: within a run, the row number over all of a bank's rows and the row number over its technical declines rise together, so their difference is constant (and never negative, because the second count can't overtake the first). The second finds run starts with LAG and ends each run at the bank's next non-technical transaction. All are a sort per bank.
Reference solution (MySQL)
WITH grouped AS (
SELECT bank_code, txn_at, status,
SUM(CASE WHEN status <> 'technical_decline' THEN 1 ELSE 0 END)
OVER (PARTITION BY bank_code ORDER BY txn_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
FROM BankTxn
)
SELECT bank_code, MIN(txn_at) AS run_start, MAX(txn_at) AS run_end, COUNT(*) AS declines
FROM grouped
WHERE status = 'technical_decline'
GROUP BY bank_code, grp
HAVING COUNT(*) >= 3
ORDER BY bank_code, run_startAnother way
WITH numbered AS (
SELECT bank_code, txn_at, status,
ROW_NUMBER() OVER (PARTITION BY bank_code ORDER BY txn_at) AS rn_all,
ROW_NUMBER() OVER (PARTITION BY bank_code, status ORDER BY txn_at) AS rn_status
FROM BankTxn
)
SELECT bank_code, MIN(txn_at) AS run_start, MAX(txn_at) AS run_end, COUNT(*) AS declines
FROM numbered WHERE status = 'technical_decline'
GROUP BY bank_code, rn_all - rn_status
HAVING COUNT(*) >= 3
ORDER BY bank_code, run_startAnother way
WITH marked AS (
SELECT bank_code, txn_at, status, LAG(status) OVER (PARTITION BY bank_code ORDER BY txn_at) AS prev_status FROM BankTxn
), starts AS (
SELECT bank_code, txn_at AS run_start FROM marked
WHERE status = 'technical_decline' AND (prev_status IS NULL OR prev_status <> 'technical_decline')
), runs AS (
SELECT s.bank_code, s.run_start,
(SELECT MIN(o.txn_at) FROM BankTxn o WHERE o.bank_code = s.bank_code AND o.txn_at > s.run_start AND o.status <> 'technical_decline') AS stop_at
FROM starts s
)
SELECT r.bank_code, r.run_start, MAX(t.txn_at) AS run_end, COUNT(*) AS declines
FROM runs r JOIN BankTxn t ON t.bank_code = r.bank_code AND t.txn_at >= r.run_start AND (r.stop_at IS NULL OR t.txn_at < r.stop_at)
GROUP BY r.bank_code, r.run_start
HAVING COUNT(*) >= 3
ORDER BY r.bank_code, r.run_start← PhonePe: Failed UPI Payments Recovered by a Retry, per Bank · PhonePe: Merchant-Spend Quartiles of Users in Each City, March 2025 →