Razorpay: MDR Pricing Rows Added, Removed or Changed Between Snapshots

Medium
914.

Razorpay: MDR Pricing Rows Added, Removed or Changed Between Snapshots

Razorpay keeps a month-end snapshot of every merchant's negotiated MDR (merchant discount rate) per payment method. Before April's invoices go out, the billing team diffs the 2025-03-31 snapshot against the 2025-04-30 one to see exactly what pricing moved.

A row is identified by (merchant_id, method). Report every row that differs between the two snapshots:

  • added — in 2025-04-30 but not in 2025-03-31;
  • removed — in 2025-03-31 but not in 2025-04-30;
  • changed — in both with a different mdr_bps.

Rows that are identical in both are left out, and snapshots on other dates are ignored. Return merchant_id, method, old_mdr_bps (NULL for an added row), new_mdr_bps (NULL for a removed row) and change_type, ordered by merchant_id, then method.

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

ColumnType
snapshot_datedate
merchant_idint
methodvarchar
mdr_bpsint

Primary key (together): snapshot_date, merchant_id, method.

One row per merchant and enabled method in each month-end snapshot. mdr_bps is the fee in basis points of the payment amount (190 = 1.90%); 0 is valid (UPI is often zero-MDR).

Example

The rows must come back in the order shown.

PricingSnapshot

snapshot_datemerchant_idmethodmdr_bps
2025-02-282emi300
2025-03-311card200
2025-03-311upi0
2025-03-312card190
2025-03-312netbanking190
2025-03-313wallet220
2025-04-301card180
2025-04-301emi250
2025-04-301upi0
2025-04-302card190
2 more rows

Output

merchant_idmethodold_mdr_bpsnew_mdr_bpschange_type
1card200180changed
1emiNULL250added
2netbanking190NULLremoved
4upiNULL0added
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: MDR Pricing Rows Added, Removed or Changed Between Snapshots

Razorpay keeps a month-end snapshot of every merchant's negotiated MDR (merchant discount rate) per payment method.

  • Difficulty: Medium
  • Topics: Joins, Subqueries, Conditional Logic
  • Dialect: MySQL
  • Problem: #914

Problem statement

Razorpay keeps a month-end snapshot of every merchant's negotiated MDR (merchant discount rate) per payment method. Before April's invoices go out, the billing team diffs the 2025-03-31 snapshot against the 2025-04-30 one to see exactly what pricing moved.

A row is identified by (merchant_id, method). Report every row that differs between the two snapshots:

  • added — in 2025-04-30 but not in 2025-03-31;
  • removed — in 2025-03-31 but not in 2025-04-30;
  • changed — in both with a different mdr_bps.

Rows that are identical in both are left out, and snapshots on other dates are ignored. Return merchant_id, method, old_mdr_bps (NULL for an added row), new_mdr_bps (NULL for a removed row) and change_type, ordered by merchant_id, then method.

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

ColumnType
snapshot_datedate
merchant_idint
methodvarchar
mdr_bpsint

Primary key: snapshot_date, merchant_id, method.

One row per merchant and enabled method in each month-end snapshot. mdr_bps is the fee in basis points of the payment amount (190 = 1.90%); 0 is valid (UPI is often zero-MDR).

Examples

Example 1

PricingSnapshot

snapshot_datemerchant_idmethodmdr_bps
2025-02-282emi300
2025-03-311card200
2025-03-311upi0
2025-03-312card190
2025-03-312netbanking190
2025-03-313wallet220
2025-04-301card180
2025-04-301emi250
2025-04-301upi0
2025-04-302card190
2025-04-303wallet220
2025-04-304upi0

Output

merchant_idmethodold_mdr_bpsnew_mdr_bpschange_type
1card200180changed
1emiNULL250added
2netbanking190NULLremoved
4upiNULL0added

How to solve Razorpay: MDR Pricing Rows Added, Removed or Changed Between Snapshots

Diffing two versions of a table is a full outer join on the key: line up the old and new rows for each (merchant, method), then classify. MySQL has no FULL OUTER JOIN, so the main query writes the three outcomes separately. added is an anti join (a 2025-04-30 row with no 2025-03-31 row for the same key), removed the mirror image, and changed an inner join of the two snapshots on the key where mdr_bps differs. Identical rows match the inner join but fail <>, so they drop out. UNION ALL is safe because the three sets cannot overlap.

Every condition names its snapshot date, so the February and May snapshots never take part — merchant 2's February EMI row is not reported as removed. A zero fee is an ordinary value: the existence of the row, not the fee, decides added or removed, which is why merchant 1's UPI row at 0 bps in both snapshots is simply unchanged.

The first alternative pivots: grouping by key with MAX(CASE WHEN snapshot_date = … THEN mdr_bps END) gives the old and new fee side by side, NULL where a snapshot lacks the row, and NOT (old <=> new) keeps only differences (NULL-safe, so added and removed rows survive). The second builds the key list with UNION and LEFT JOINs each snapshot to it. All three read the two snapshots a small, fixed number of times; with the primary key starting at snapshot_date, each lookup is an index probe.

Reference solution (MySQL)

SELECT n.merchant_id, n.method, NULL AS old_mdr_bps, n.mdr_bps AS new_mdr_bps, 'added' AS change_type
FROM PricingSnapshot n
WHERE n.snapshot_date = '2025-04-30'
  AND NOT EXISTS (SELECT 1 FROM PricingSnapshot o WHERE o.snapshot_date = '2025-03-31'
                  AND o.merchant_id = n.merchant_id AND o.method = n.method)
UNION ALL
SELECT o.merchant_id, o.method, o.mdr_bps, NULL, 'removed'
FROM PricingSnapshot o
WHERE o.snapshot_date = '2025-03-31'
  AND NOT EXISTS (SELECT 1 FROM PricingSnapshot n WHERE n.snapshot_date = '2025-04-30'
                  AND n.merchant_id = o.merchant_id AND n.method = o.method)
UNION ALL
SELECT o.merchant_id, o.method, o.mdr_bps, n.mdr_bps, 'changed'
FROM PricingSnapshot o
JOIN PricingSnapshot n ON n.merchant_id = o.merchant_id AND n.method = o.method
 AND n.snapshot_date = '2025-04-30'
WHERE o.snapshot_date = '2025-03-31' AND o.mdr_bps <> n.mdr_bps
ORDER BY merchant_id, method

Another way

SELECT merchant_id, method, old_mdr_bps, new_mdr_bps,
       CASE WHEN old_mdr_bps IS NULL THEN 'added' WHEN new_mdr_bps IS NULL THEN 'removed' ELSE 'changed' END AS change_type
FROM (
  SELECT merchant_id, method,
         MAX(CASE WHEN snapshot_date = '2025-03-31' THEN mdr_bps END) AS old_mdr_bps,
         MAX(CASE WHEN snapshot_date = '2025-04-30' THEN mdr_bps END) AS new_mdr_bps
  FROM PricingSnapshot
  WHERE snapshot_date IN ('2025-03-31', '2025-04-30')
  GROUP BY merchant_id, method
) d
WHERE NOT (old_mdr_bps <=> new_mdr_bps)
ORDER BY merchant_id, method

Another way

WITH k AS (
  SELECT merchant_id, method FROM PricingSnapshot WHERE snapshot_date = '2025-03-31'
  UNION
  SELECT merchant_id, method FROM PricingSnapshot WHERE snapshot_date = '2025-04-30'
)
SELECT k.merchant_id, k.method, o.mdr_bps AS old_mdr_bps, n.mdr_bps AS new_mdr_bps,
       IF(o.mdr_bps IS NULL, 'added', IF(n.mdr_bps IS NULL, 'removed', 'changed')) AS change_type
FROM k
LEFT JOIN PricingSnapshot o ON o.merchant_id = k.merchant_id AND o.method = k.method AND o.snapshot_date = '2025-03-31'
LEFT JOIN PricingSnapshot n ON n.merchant_id = k.merchant_id AND n.method = k.method AND n.snapshot_date = '2025-04-30'
WHERE o.mdr_bps IS NULL OR n.mdr_bps IS NULL OR o.mdr_bps <> n.mdr_bps
ORDER BY k.merchant_id, k.method

← Razorpay: Settlements That Don't Match Their Line Items · Razorpay: Bank Credits That Match No Settlement →