Razorpay: Orders Rescued by Switching Payment Method
When a payment fails, Razorpay Checkout lets the shopper retry the same order, often with another method.
- Difficulty: Hard
- Topics: Window Functions, Subqueries, Aggregation
- Dialect: MySQL
- Problem: #919
Problem statement
When a payment fails, Razorpay Checkout lets the shopper retry the same order, often with another method. The checkout team wants to know which failing methods are rescued by a switch, and by what.
An order's first attempt is its earliest attempted_at (ties by smaller payment_id). An order is rescued when its first attempt failed and the order was later captured with a method different from the first attempt's. For every method that was the failed first attempt of at least one order, return first_method, failed_first (orders whose first attempt failed with it), rescued_by_switch (of those, the rescued ones) and top_rescue_method — the method that captured the most of those rescued orders (ties: alphabetically first; NULL when none was rescued). Any order.
A practice scenario set at Razorpay: the tables and data are invented, and this is not a question from, or endorsed by, Razorpay.
Tables
Table: PaymentAttempt
| Column | Type |
|---|---|
| payment_id | int |
| order_id | int |
| method | varchar |
| attempted_at | datetime |
| status | enum(failed, captured) |
Primary key: payment_id.
One row per payment attempt on an order. An order is captured at most once, and no attempt follows its capture.
Examples
Example 1
PaymentAttempt
| payment_id | order_id | method | attempted_at | status |
|---|---|---|---|---|
| 1 | 100 | card | 2025-03-01 10:00:00 | failed |
| 2 | 100 | upi | 2025-03-01 10:02:00 | captured |
| 3 | 101 | card | 2025-03-01 11:00:00 | failed |
| 4 | 101 | card | 2025-03-01 11:01:00 | captured |
| 5 | 102 | card | 2025-03-01 12:00:00 | failed |
| 6 | 102 | netbanking | 2025-03-01 12:03:00 | failed |
| 7 | 102 | upi | 2025-03-01 12:05:00 | captured |
| 8 | 103 | upi | 2025-03-01 13:00:00 | failed |
| 9 | 103 | upi | 2025-03-01 13:01:00 | failed |
| 10 | 104 | upi | 2025-03-01 14:00:00 | captured |
| 11 | 105 | netbanking | 2025-03-01 15:00:00 | failed |
| 12 | 105 | card | 2025-03-01 15:04:00 | captured |
Output
| first_method | failed_first | rescued_by_switch | top_rescue_method |
|---|---|---|---|
| card | 3 | 2 | upi |
| netbanking | 1 | 1 | card |
| upi | 1 | 0 | NULL |
How to solve Razorpay: Orders Rescued by Switching Payment Method
Build it in stages with CTEs. ranked numbers each order's attempts by time with payment_id as tie-breaker, so the first attempt is unambiguous. firsts keeps the orders whose first attempt failed, with that attempt's method — one row per order, which is the population of the report.
rescues joins those orders to their captured attempt when the capture used a different method. An order captured with the same method (a plain retry) is not a rescue by switching; an order never captured is not rescued at all. Since an order is captured at most once, each rescued order contributes exactly one row.
tops counts rescues per (first method, rescue method) and ranks them with ROW_NUMBER() … ORDER BY COUNT(*) DESC, rescue_method, so a tie goes to the alphabetically first method and exactly one row per first method gets rank 1. The final query groups firsts per method and LEFT JOINs the rank-1 row, so a method nobody was rescued from still appears with rescued_by_switch = 0 and a NULL top method.
The alternative finds first attempts with NOT EXISTS (no earlier attempt on the order), the rescue method with a scalar subquery, and the top method with ORDER BY … LIMIT 1 in a correlated subquery. The window version sorts each order once; the subquery version probes per order and is fine with an index on (order_id, attempted_at).
Reference solution (MySQL)
WITH ranked AS (
SELECT order_id, method, status,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY attempted_at, payment_id) AS rn
FROM PaymentAttempt
), firsts AS (
SELECT order_id, method AS first_method FROM ranked WHERE rn = 1 AND status = 'failed'
), rescues AS (
SELECT f.first_method, a.method AS rescue_method
FROM firsts f
JOIN PaymentAttempt a ON a.order_id = f.order_id AND a.status = 'captured' AND a.method <> f.first_method
), tops AS (
SELECT first_method, rescue_method,
ROW_NUMBER() OVER (PARTITION BY first_method ORDER BY COUNT(*) DESC, rescue_method) AS r
FROM rescues
GROUP BY first_method, rescue_method
)
SELECT f.first_method, COUNT(*) AS failed_first,
(SELECT COUNT(*) FROM rescues x WHERE x.first_method = f.first_method) AS rescued_by_switch,
t.rescue_method AS top_rescue_method
FROM firsts f
LEFT JOIN tops t ON t.first_method = f.first_method AND t.r = 1
GROUP BY f.first_method, t.rescue_methodAnother way
WITH firsts AS (
SELECT a.order_id, a.method AS first_method
FROM PaymentAttempt a
WHERE a.status = 'failed'
AND NOT EXISTS (SELECT 1 FROM PaymentAttempt b WHERE b.order_id = a.order_id
AND (b.attempted_at < a.attempted_at OR (b.attempted_at = a.attempted_at AND b.payment_id < a.payment_id)))
), outcome AS (
SELECT f.order_id, f.first_method,
(SELECT c.method FROM PaymentAttempt c WHERE c.order_id = f.order_id AND c.status = 'captured' AND c.method <> f.first_method) AS rescue_method
FROM firsts f
), counts AS (
SELECT first_method, rescue_method, COUNT(*) AS n FROM outcome WHERE rescue_method IS NOT NULL GROUP BY first_method, rescue_method
)
SELECT o.first_method, COUNT(*) AS failed_first, COUNT(o.rescue_method) AS rescued_by_switch,
(SELECT c.rescue_method FROM counts c WHERE c.first_method = o.first_method ORDER BY c.n DESC, c.rescue_method LIMIT 1) AS top_rescue_method
FROM outcome o
GROUP BY o.first_method← Razorpay: Diwali-Week Captured Volume Against Last Year's Diwali Week · Razorpay: Queued Payouts Rejected for Insufficient Balance →