Razorpay: Chargeback Rate by Merchant Category

Medium
907.

Razorpay: Chargeback Rate by Merchant Category

MediumJoinsAggregation

MySQL

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

ColumnType
merchant_idint
business_namevarchar
categoryvarchar

Primary key: merchant_id.

One row per merchant.

Table: Payment

ColumnTypeValues
payment_idint
merchant_idint
amountint
statusenumcaptured, failed

Primary key: payment_id.

One row per payment.

Table: Dispute

ColumnTypeValues
dispute_idint
payment_idint
phaseenumchargeback, pre_arbitration, arbitration
opened_ondate

Primary key: dispute_id.

One row per dispute phase raised on a payment, so a payment can have several rows.

Example

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

Merchant

merchant_idbusiness_namecategory
1Kirana Kartecommerce
2Urban Threadsecommerce
3SkillSprint Academyeducation
4TrekNesttravel

Payment

payment_idmerchant_idamountstatus
1011200captured
111800captured
1224500captured
132300failed
1439999captured
1534999captured
16415000failed

Dispute

dispute_idpayment_idphaseopened_on
10010chargeback2025-02-10
10110pre_arbitration2025-03-05
10212chargeback2025-02-20

Output

categorycaptured_paymentsdisputed_paymentsdispute_rate_pct
ecommerce3266.67
education200
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: 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

ColumnType
merchant_idint
business_namevarchar
categoryvarchar

Primary key: merchant_id.

One row per merchant.

Table: Payment

ColumnType
payment_idint
merchant_idint
amountint
statusenum(captured, failed)

Primary key: payment_id.

One row per payment.

Table: Dispute

ColumnType
dispute_idint
payment_idint
phaseenum(chargeback, pre_arbitration, arbitration)
opened_ondate

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_idbusiness_namecategory
1Kirana Kartecommerce
2Urban Threadsecommerce
3SkillSprint Academyeducation
4TrekNesttravel

Payment

payment_idmerchant_idamountstatus
1011200captured
111800captured
1224500captured
132300failed
1439999captured
1534999captured
16415000failed

Dispute

dispute_idpayment_idphaseopened_on
10010chargeback2025-02-10
10110pre_arbitration2025-03-05
10212chargeback2025-02-20

Output

categorycaptured_paymentsdisputed_paymentsdispute_rate_pct
ecommerce3266.67
education200

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.category

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