Razorpay: Merchants Whose Weekly Success Rate Fell 10+ Points

Hard
921.

Razorpay: Merchants Whose Weekly Success Rate Fell 10+ Points

Razorpay's merchant health team flags merchants whose payment success rate drops sharply from one week to the next — often a sign of a broken checkout integration.

Weeks start on Monday. A merchant's weekly success rate is 100 × captured / attempts over its payments created that week. Compare each week with the merchant's immediately preceding calendar week (exactly 7 days earlier), only when that week also had payments. Report the weeks where the rate fell by 10 points or more: merchant_id, week_start (the Monday), previous_rate and current_rate, each rate rounded to 2 decimals (decide the drop on the exact rates). Order by merchant_id, then week_start.

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: Payment

ColumnTypeValues
payment_idint
merchant_idint
created_ondate
statusenumcaptured, failed

Primary key: payment_id.

One row per payment attempt.

Example

The rows must come back in the order shown.

Payment

payment_idmerchant_idcreated_onstatus
18012025-02-03captured
28012025-02-05captured
38012025-02-09captured
48012025-02-10captured
58012025-02-12failed
68012025-02-24failed
78022025-02-04captured
88022025-02-06captured
98022025-02-07captured
108022025-02-08captured
5 more rows

Output

merchant_idweek_startprevious_ratecurrent_rate
8012025-02-1010050
8022025-02-1010075
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: Merchants Whose Weekly Success Rate Fell 10+ Points

Razorpay's merchant health team flags merchants whose payment success rate drops sharply from one week to the next — often a sign of a broken checkout…

  • Difficulty: Hard
  • Topics: Window Functions, Dates, Aggregation
  • Dialect: MySQL
  • Problem: #921

Problem statement

Razorpay's merchant health team flags merchants whose payment success rate drops sharply from one week to the next — often a sign of a broken checkout integration.

Weeks start on Monday. A merchant's weekly success rate is 100 × captured / attempts over its payments created that week. Compare each week with the merchant's immediately preceding calendar week (exactly 7 days earlier), only when that week also had payments. Report the weeks where the rate fell by 10 points or more: merchant_id, week_start (the Monday), previous_rate and current_rate, each rate rounded to 2 decimals (decide the drop on the exact rates). Order by merchant_id, then week_start.

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: Payment

ColumnType
payment_idint
merchant_idint
created_ondate
statusenum(captured, failed)

Primary key: payment_id.

One row per payment attempt.

Examples

Example 1

Payment

payment_idmerchant_idcreated_onstatus
18012025-02-03captured
28012025-02-05captured
38012025-02-09captured
48012025-02-10captured
58012025-02-12failed
68012025-02-24failed
78022025-02-04captured
88022025-02-06captured
98022025-02-07captured
108022025-02-08captured
118022025-02-08captured
128022025-02-11captured
138022025-02-13captured
148022025-02-14captured
158022025-02-16failed

Output

merchant_idweek_startprevious_ratecurrent_rate
8012025-02-1010050
8022025-02-1010075

How to solve Razorpay: Merchants Whose Weekly Success Rate Fell 10+ Points

Start by bucketing payments into Monday-start weeks: WEEKDAY counts days since Monday, so subtracting it from the date gives the week's Monday. Grouping by merchant and that Monday gives each week's attempts and captures.

Next, pair each week with the previous one. LAG over the merchant's weeks returns the previous row, but a merchant with no payments in some week has a gap, and the statement wants the immediately preceding calendar week only — so the pair is kept only when the two Mondays are exactly 7 days apart. A self join on prev.week_start = cur.week_start − 7 days (the alternative) expresses the same thing directly.

The drop test is done on exact values. Rates such as 66.666… and 56.666… differ by exactly 10, but comparing rounded or floating values can tip such a boundary either way. Cross-multiplying keeps it in integers: (prev_ok/prev_attempts − ok/attempts) ≥ 1/10 becomes 10 * (prev_ok * attempts − ok * prev_attempts) >= prev_attempts * attempts. Only the displayed rates are rounded. Both versions aggregate once and then work on one row per merchant-week.

Reference solution (MySQL)

WITH weekly AS (
  SELECT merchant_id, week_start, COUNT(*) AS attempts, SUM(status = 'captured') AS ok
  FROM (SELECT merchant_id, status, DATE_SUB(created_on, INTERVAL WEEKDAY(created_on) DAY) AS week_start FROM Payment) p
  GROUP BY merchant_id, week_start
), paired AS (
  SELECT merchant_id, week_start, attempts, ok,
         LAG(week_start) OVER (PARTITION BY merchant_id ORDER BY week_start) AS prev_week,
         LAG(attempts) OVER (PARTITION BY merchant_id ORDER BY week_start) AS prev_attempts,
         LAG(ok) OVER (PARTITION BY merchant_id ORDER BY week_start) AS prev_ok
  FROM weekly
)
SELECT merchant_id, week_start,
       ROUND(100 * prev_ok / prev_attempts, 2) AS previous_rate,
       ROUND(100 * ok / attempts, 2) AS current_rate
FROM paired
WHERE DATEDIFF(week_start, prev_week) = 7
  AND 10 * (prev_ok * attempts - ok * prev_attempts) >= prev_attempts * attempts
ORDER BY merchant_id, week_start

Another way

WITH weekly AS (
  SELECT merchant_id, DATE_SUB(created_on, INTERVAL WEEKDAY(created_on) DAY) AS week_start,
         COUNT(*) AS attempts, SUM(CASE WHEN status = 'captured' THEN 1 ELSE 0 END) AS ok
  FROM Payment
  GROUP BY merchant_id, DATE_SUB(created_on, INTERVAL WEEKDAY(created_on) DAY)
)
SELECT cur.merchant_id, cur.week_start,
       ROUND(100 * prev.ok / prev.attempts, 2) AS previous_rate,
       ROUND(100 * cur.ok / cur.attempts, 2) AS current_rate
FROM weekly cur
JOIN weekly prev ON prev.merchant_id = cur.merchant_id AND prev.week_start = DATE_SUB(cur.week_start, INTERVAL 7 DAY)
WHERE 10 * (prev.ok * cur.attempts - cur.ok * prev.attempts) >= prev.attempts * cur.attempts
ORDER BY cur.merchant_id, cur.week_start

← Razorpay: Queued Payouts Rejected for Insufficient Balance · Razorpay: Subscription Billing Cycles With No Charge Attempt →