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
| Column | Type |
|---|---|
| payment_id | int |
| merchant_id | int |
| created_on | date |
| status | enum(captured, failed) |
Primary key: payment_id.
One row per payment attempt.
Examples
Example 1
Payment
| payment_id | merchant_id | created_on | status |
|---|---|---|---|
| 1 | 801 | 2025-02-03 | captured |
| 2 | 801 | 2025-02-05 | captured |
| 3 | 801 | 2025-02-09 | captured |
| 4 | 801 | 2025-02-10 | captured |
| 5 | 801 | 2025-02-12 | failed |
| 6 | 801 | 2025-02-24 | failed |
| 7 | 802 | 2025-02-04 | captured |
| 8 | 802 | 2025-02-06 | captured |
| 9 | 802 | 2025-02-07 | captured |
| 10 | 802 | 2025-02-08 | captured |
| 11 | 802 | 2025-02-08 | captured |
| 12 | 802 | 2025-02-11 | captured |
| 13 | 802 | 2025-02-13 | captured |
| 14 | 802 | 2025-02-14 | captured |
| 15 | 802 | 2025-02-16 | failed |
Output
| merchant_id | week_start | previous_rate | current_rate |
|---|---|---|---|
| 801 | 2025-02-10 | 100 | 50 |
| 802 | 2025-02-10 | 100 | 75 |
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_startAnother 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 →