PhonePe: Failed UPI Payments Recovered by a Retry, per Bank
When a UPI payment fails, many users simply try again. Payments product wants to know, per remitter bank, how often a failed payment is rescued by a retry.
- Difficulty: Hard
- Topics: Window Functions, Dates, Aggregation
- Dialect: MySQL
- Problem: #892
Problem statement
When a UPI payment fails, many users simply try again. Payments product wants to know, per remitter bank, how often a failed payment is rescued by a retry.
Attempts by the same user_id to the same payee_vpa for the same amount form one chain as long as each attempt comes at most 10 minutes after the previous one (a gap of exactly 10 minutes still continues the chain; a longer gap starts a new one). A chain whose first attempt failed is a failed chain; it is recovered if any attempt in it succeeded. For each remitter bank of a failed chain's first attempt, return remitter_bank, failed_chains, recovered_chains and recovery_pct = 100 × recovered ÷ failed rounded to 2 decimals. Banks with no failed chain do not appear. A user never makes two attempts at the same moment. Order the rows by remitter_bank.
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: PaymentAttempt
| Column | Type |
|---|---|
| attempt_id | int |
| user_id | int |
| payee_vpa | varchar |
| amount | int |
| remitter_bank | varchar |
| status | enum(success, failed) |
| attempted_at | datetime |
Primary key: attempt_id.
One row per payment attempt; a retry may go through a different linked bank.
Examples
Example 1
PaymentAttempt
| attempt_id | user_id | payee_vpa | amount | remitter_bank | status | attempted_at |
|---|---|---|---|---|---|---|
| 1 | 1 | shop.fresh@ybl | 250 | SBI | failed | 2025-09-01 10:00:00 |
| 2 | 1 | shop.fresh@ybl | 250 | SBI | failed | 2025-09-01 10:04:00 |
| 3 | 1 | shop.fresh@ybl | 250 | SBI | success | 2025-09-01 10:09:00 |
| 4 | 1 | shop.fresh@ybl | 250 | SBI | failed | 2025-09-01 10:30:00 |
| 5 | 2 | rent.owner@ibl | 15000 | HDFC | failed | 2025-09-01 09:00:00 |
| 6 | 2 | rent.owner@ibl | 15000 | ICICI | success | 2025-09-01 09:10:00 |
| 7 | 2 | kabir@ybl | 500 | HDFC | failed | 2025-09-01 12:00:00 |
| 8 | 2 | kabir@ybl | 500 | HDFC | success | 2025-09-01 12:11:00 |
| 9 | 3 | chai.point@ybl | 100 | ICICI | success | 2025-09-01 08:00:00 |
| 10 | 3 | chai.point@ybl | 200 | ICICI | failed | 2025-09-01 08:05:00 |
| 11 | 3 | chai.point@ybl | 100 | ICICI | success | 2025-09-01 08:07:00 |
Output
| remitter_bank | failed_chains | recovered_chains | recovery_pct |
|---|---|---|---|
| HDFC | 2 | 1 | 50 |
| ICICI | 1 | 0 | 0 |
| SBI | 2 | 1 | 50 |
How to solve PhonePe: Failed UPI Payments Recovered by a Retry, per Bank
The chains are sessions, and sessionising is a two-window pattern. LAG(attempted_at) over the attempts of one (user, payee, amount) in time order gives the previous attempt; the row starts a new chain when the gap is more than 600 seconds or there is no previous attempt (the TIMESTAMPDIFF is NULL, the comparison is unknown and the CASE falls to ELSE 1). A running SUM of that flag then gives every attempt its chain number.
Grouping by key and chain number yields each chain's start time and a MAX of "was a success", which is the recovered flag. Joining back on the start time picks up the first attempt's status and bank — times are unique per user, so the match is exact. Only chains whose first attempt failed are counted, grouped by that attempt's bank. In the example user 2's rent payment failed on HDFC and succeeded on ICICI 10 minutes later: one HDFC chain, recovered. Their 500-rupee retry came 11 minutes later — a new chain, so the HDFC failure stays unrecovered.
Recovery is decided per chain, so a chain is counted once even when it held several retries. The alternative finds chain starts with NOT EXISTS (no attempt of the same key in the 10 minutes before) and assigns each attempt to the latest start at or before it; it is correct but quadratic, while the window version sorts each key once.
Reference solution (MySQL)
WITH flagged AS (
SELECT user_id, payee_vpa, amount, remitter_bank, status, attempted_at,
CASE WHEN TIMESTAMPDIFF(SECOND,
LAG(attempted_at) OVER (PARTITION BY user_id, payee_vpa, amount ORDER BY attempted_at),
attempted_at) <= 600 THEN 0 ELSE 1 END AS new_chain
FROM PaymentAttempt
), numbered AS (
SELECT f.*,
SUM(new_chain) OVER (PARTITION BY user_id, payee_vpa, amount ORDER BY attempted_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS chain_no
FROM flagged f
), chains AS (
SELECT user_id, payee_vpa, amount, chain_no, MIN(attempted_at) AS started_at,
MAX(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS recovered
FROM numbered
GROUP BY user_id, payee_vpa, amount, chain_no
)
SELECT n.remitter_bank, COUNT(*) AS failed_chains, SUM(c.recovered) AS recovered_chains,
ROUND(100 * SUM(c.recovered) / COUNT(*), 2) AS recovery_pct
FROM chains c
JOIN numbered n ON n.user_id = c.user_id AND n.payee_vpa = c.payee_vpa
AND n.amount = c.amount AND n.attempted_at = c.started_at
WHERE n.status = 'failed'
GROUP BY n.remitter_bank
ORDER BY n.remitter_bankAnother way
WITH starts AS (
SELECT p.* FROM PaymentAttempt p
WHERE NOT EXISTS (SELECT 1 FROM PaymentAttempt q WHERE q.user_id = p.user_id AND q.payee_vpa = p.payee_vpa AND q.amount = p.amount
AND q.attempted_at < p.attempted_at AND q.attempted_at >= DATE_SUB(p.attempted_at, INTERVAL 10 MINUTE))
), member AS (
SELECT x.user_id, x.payee_vpa, x.amount, x.status,
(SELECT MAX(s.attempted_at) FROM starts s WHERE s.user_id = x.user_id AND s.payee_vpa = x.payee_vpa AND s.amount = x.amount AND s.attempted_at <= x.attempted_at) AS chain_start
FROM PaymentAttempt x
)
SELECT s.remitter_bank, COUNT(*) AS failed_chains,
SUM(CASE WHEN EXISTS (SELECT 1 FROM member m WHERE m.user_id = s.user_id AND m.payee_vpa = s.payee_vpa AND m.amount = s.amount AND m.chain_start = s.attempted_at AND m.status = 'success') THEN 1 ELSE 0 END) AS recovered_chains,
ROUND(100 * SUM(CASE WHEN EXISTS (SELECT 1 FROM member m WHERE m.user_id = s.user_id AND m.payee_vpa = s.payee_vpa AND m.amount = s.amount AND m.chain_start = s.attempted_at AND m.status = 'success') THEN 1 ELSE 0 END) / COUNT(*), 2) AS recovery_pct
FROM starts s
WHERE s.status = 'failed'
GROUP BY s.remitter_bank
ORDER BY s.remitter_bank← PhonePe: Users Paying Three New Payees Within One Hour · PhonePe: Bank Downtime Runs From Consecutive Technical Declines →