Razorpay: Chargeback Rate by Merchant Category — SQL Problem
Razorpay's risk team watches the share of captured payments that turn into disputes, per merchant category — card networks penalise high chargeback ratios.
- Difficulty: Medium
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #907
Problem statement
Razorpay's risk team watches the share of captured payments that turn into disputes, per merchant category — card networks penalise high chargeback ratios.
For every merchant category with at least one captured payment, return category, captured_payments, disputed_payments (captured payments with at least one dispute row — a payment that went from chargeback to pre-arbitration still counts once) and dispute_rate_pct = 100 × disputed / captured, rounded to 2 decimals. Categories with no disputes show 0. 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: Merchant
| Column | Type |
|---|---|
| merchant_id | int |
| business_name | varchar |
| category | varchar |
Primary key: merchant_id.
One row per merchant.
Table: Payment
| Column | Type |
|---|---|
| payment_id | int |
| merchant_id | int |
| amount | int |
| status | enum(captured, failed) |
Primary key: payment_id.
One row per payment.
Table: Dispute
| Column | Type |
|---|---|
| dispute_id | int |
| payment_id | int |
| phase | enum(chargeback, pre_arbitration, arbitration) |
| opened_on | date |
Primary key: dispute_id.
One row per dispute phase raised on a payment, so a payment can have several rows.
Examples
Example 1
Merchant
| merchant_id | business_name | category |
|---|---|---|
| 1 | Kirana Kart | ecommerce |
| 2 | Urban Threads | ecommerce |
| 3 | SkillSprint Academy | education |
| 4 | TrekNest | travel |
Payment
| payment_id | merchant_id | amount | status |
|---|---|---|---|
| 10 | 1 | 1200 | captured |
| 11 | 1 | 800 | captured |
| 12 | 2 | 4500 | captured |
| 13 | 2 | 300 | failed |
| 14 | 3 | 9999 | captured |
| 15 | 3 | 4999 | captured |
| 16 | 4 | 15000 | failed |
Dispute
| dispute_id | payment_id | phase | opened_on |
|---|---|---|---|
| 100 | 10 | chargeback | 2025-02-10 |
| 101 | 10 | pre_arbitration | 2025-03-05 |
| 102 | 12 | chargeback | 2025-02-20 |
Output
| category | captured_payments | disputed_payments | dispute_rate_pct |
|---|---|---|---|
| ecommerce | 3 | 2 | 66.67 |
| education | 2 | 0 | 0 |
How to solve Razorpay: Chargeback Rate by Merchant Category
Start from captured payments joined to their merchant's category, then bring in disputes with a LEFT JOIN so undisputed payments keep a row (with NULL dispute columns) and still count as captured.
The trap is fan-out: a payment with a chargeback and a pre-arbitration row appears twice after the join, so COUNT(*) would count it twice as captured and twice as disputed. COUNT(DISTINCT p.payment_id) counts each captured payment once, and COUNT(DISTINCT d.payment_id) counts each disputed payment once — NULLs from undisputed payments are ignored by COUNT, which is what makes a category with no disputes show 0.
The alternative avoids the fan-out altogether: it marks every captured payment with EXISTS (1 if any dispute row exists) and then simply counts and sums per category. That version is usually the cheaper one with an index on Dispute(payment_id), because each payment is probed once instead of being multiplied and de-duplicated.
Reference solution (MySQL)
SELECT m.category,
COUNT(DISTINCT p.payment_id) AS captured_payments,
COUNT(DISTINCT d.payment_id) AS disputed_payments,
ROUND(100 * COUNT(DISTINCT d.payment_id) / COUNT(DISTINCT p.payment_id), 2) AS dispute_rate_pct
FROM Merchant m
JOIN Payment p ON p.merchant_id = m.merchant_id AND p.status = 'captured'
LEFT JOIN Dispute d ON d.payment_id = p.payment_id
GROUP BY m.categoryAnother way
SELECT category, COUNT(*) AS captured_payments, SUM(disputed) AS disputed_payments,
ROUND(100 * SUM(disputed) / COUNT(*), 2) AS dispute_rate_pct
FROM (SELECT m.category,
CASE WHEN EXISTS (SELECT 1 FROM Dispute d WHERE d.payment_id = p.payment_id) THEN 1 ELSE 0 END AS disputed
FROM Payment p JOIN Merchant m ON m.merchant_id = p.merchant_id
WHERE p.status = 'captured') t
GROUP BY category← Razorpay: Card Payment Success Rate by Issuing Bank · Razorpay: Webhook Events That Never Got a 2xx Response →