PhonePe: Bank Downtime Runs From Consecutive Technical Declines

Hard
893.

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. 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

ColumnTypeValues
txn_idint
bank_codevarchar
txn_atdatetime
statusenumsuccess, business_decline, technical_decline

Primary key: txn_id.

One row per UPI transaction routed to the bank.

Example

The rows must come back in the order shown.

BankTxn

txn_idbank_codetxn_atstatus
1SBI2025-10-03 10:00:00success
2SBI2025-10-03 10:01:00technical_decline
3SBI2025-10-03 10:02:00technical_decline
4SBI2025-10-03 10:03:00technical_decline
5SBI2025-10-03 10:04:00business_decline
6SBI2025-10-03 10:05:00technical_decline
7SBI2025-10-03 10:06:00technical_decline
8SBI2025-10-03 10:07:00success
9HDFC2025-10-03 11:00:00success
10HDFC2025-10-03 11:02:00technical_decline
4 more rows

Output

bank_coderun_startrun_enddeclines
HDFC2025-10-03 11:02:002025-10-03 11:09:004
SBI2025-10-03 10:01:002025-10-03 10:03:003
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.

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

ColumnType
txn_idint
bank_codevarchar
txn_atdatetime
statusenum(success, business_decline, technical_decline)

Primary key: txn_id.

One row per UPI transaction routed to the bank.

Examples

Example 1

BankTxn

txn_idbank_codetxn_atstatus
1SBI2025-10-03 10:00:00success
2SBI2025-10-03 10:01:00technical_decline
3SBI2025-10-03 10:02:00technical_decline
4SBI2025-10-03 10:03:00technical_decline
5SBI2025-10-03 10:04:00business_decline
6SBI2025-10-03 10:05:00technical_decline
7SBI2025-10-03 10:06:00technical_decline
8SBI2025-10-03 10:07:00success
9HDFC2025-10-03 11:00:00success
10HDFC2025-10-03 11:02:00technical_decline
11HDFC2025-10-03 11:03:00technical_decline
12ICICI2025-10-03 11:04:00technical_decline
13HDFC2025-10-03 11:05:00technical_decline
14HDFC2025-10-03 11:09:00technical_decline

Output

bank_coderun_startrun_enddeclines
HDFC2025-10-03 11:02:002025-10-03 11:09:004
SBI2025-10-03 10:01:002025-10-03 10:03:003

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_start

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

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