PhonePe: Failed UPI Payments Recovered by a Retry, per Bank

Hard
892.

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.

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

ColumnTypeValues
attempt_idint
user_idint
payee_vpavarchar
amountint
remitter_bankvarchar
statusenumsuccess, failed
attempted_atdatetime

Primary key: attempt_id.

One row per payment attempt; a retry may go through a different linked bank.

Example

The rows must come back in the order shown.

PaymentAttempt

attempt_iduser_idpayee_vpaamountremitter_bankstatusattempted_at
11shop.fresh@ybl250SBIfailed2025-09-01 10:00:00
21shop.fresh@ybl250SBIfailed2025-09-01 10:04:00
31shop.fresh@ybl250SBIsuccess2025-09-01 10:09:00
41shop.fresh@ybl250SBIfailed2025-09-01 10:30:00
52rent.owner@ibl15000HDFCfailed2025-09-01 09:00:00
62rent.owner@ibl15000ICICIsuccess2025-09-01 09:10:00
72kabir@ybl500HDFCfailed2025-09-01 12:00:00
82kabir@ybl500HDFCsuccess2025-09-01 12:11:00
93chai.point@ybl100ICICIsuccess2025-09-01 08:00:00
103chai.point@ybl200ICICIfailed2025-09-01 08:05:00
1 more row

Output

remitter_bankfailed_chainsrecovered_chainsrecovery_pct
HDFC2150
ICICI100
SBI2150
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: 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

ColumnType
attempt_idint
user_idint
payee_vpavarchar
amountint
remitter_bankvarchar
statusenum(success, failed)
attempted_atdatetime

Primary key: attempt_id.

One row per payment attempt; a retry may go through a different linked bank.

Examples

Example 1

PaymentAttempt

attempt_iduser_idpayee_vpaamountremitter_bankstatusattempted_at
11shop.fresh@ybl250SBIfailed2025-09-01 10:00:00
21shop.fresh@ybl250SBIfailed2025-09-01 10:04:00
31shop.fresh@ybl250SBIsuccess2025-09-01 10:09:00
41shop.fresh@ybl250SBIfailed2025-09-01 10:30:00
52rent.owner@ibl15000HDFCfailed2025-09-01 09:00:00
62rent.owner@ibl15000ICICIsuccess2025-09-01 09:10:00
72kabir@ybl500HDFCfailed2025-09-01 12:00:00
82kabir@ybl500HDFCsuccess2025-09-01 12:11:00
93chai.point@ybl100ICICIsuccess2025-09-01 08:00:00
103chai.point@ybl200ICICIfailed2025-09-01 08:05:00
113chai.point@ybl100ICICIsuccess2025-09-01 08:07:00

Output

remitter_bankfailed_chainsrecovered_chainsrecovery_pct
HDFC2150
ICICI100
SBI2150

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_bank

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