Razorpay: Orders Rescued by Switching Payment Method

Hard
919.

Razorpay: Orders Rescued by Switching Payment Method

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

ColumnTypeValues
payment_idint
order_idint
methodvarchar
attempted_atdatetime
statusenumfailed, 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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

PaymentAttempt

payment_idorder_idmethodattempted_atstatus
1100card2025-03-01 10:00:00failed
2100upi2025-03-01 10:02:00captured
3101card2025-03-01 11:00:00failed
4101card2025-03-01 11:01:00captured
5102card2025-03-01 12:00:00failed
6102netbanking2025-03-01 12:03:00failed
7102upi2025-03-01 12:05:00captured
8103upi2025-03-01 13:00:00failed
9103upi2025-03-01 13:01:00failed
10104upi2025-03-01 14:00:00captured
2 more rows

Output

first_methodfailed_firstrescued_by_switchtop_rescue_method
card32upi
netbanking11card
upi10NULL
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.

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

ColumnType
payment_idint
order_idint
methodvarchar
attempted_atdatetime
statusenum(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_idorder_idmethodattempted_atstatus
1100card2025-03-01 10:00:00failed
2100upi2025-03-01 10:02:00captured
3101card2025-03-01 11:00:00failed
4101card2025-03-01 11:01:00captured
5102card2025-03-01 12:00:00failed
6102netbanking2025-03-01 12:03:00failed
7102upi2025-03-01 12:05:00captured
8103upi2025-03-01 13:00:00failed
9103upi2025-03-01 13:01:00failed
10104upi2025-03-01 14:00:00captured
11105netbanking2025-03-01 15:00:00failed
12105card2025-03-01 15:04:00captured

Output

first_methodfailed_firstrescued_by_switchtop_rescue_method
card32upi
netbanking11card
upi10NULL

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_method

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