Razorpay: Merchants Whose Live Methods Differ From Their Request
At onboarding, a merchant asks Razorpay for a set of payment methods (UPI, cards, EMI, Pay Later …); risk and ops then activate and deactivate methods over…
- Difficulty: Hard
- Topics: Subqueries, Strings, Dates, Aggregation
- Dialect: MySQL
- Problem: #917
Problem statement
At onboarding, a merchant asks Razorpay for a set of payment methods (UPI, cards, EMI, Pay Later …); risk and ops then activate and deactivate methods over time. An activation audit compares, for every merchant, the methods it asked for with the methods live today.
A method is live on 2025-03-31 for a merchant when it has at least one activation with activated_on on or before 2025-03-31 and deactivated_on NULL or after 2025-03-31. For every merchant whose set of live methods is not exactly its set of requested methods, return merchant_id, missing_methods (requested but not live) and extra_methods (live but never requested) — each a comma-separated list with no spaces, in alphabetical order, without duplicates, and NULL when empty. Consider every merchant that appears in either table. 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: MethodRequest
| Column | Type |
|---|---|
| merchant_id | int |
| method | varchar |
Primary key: merchant_id, method.
The methods each merchant asked for on its onboarding form. Method names are lower case.
Table: MethodActivation
| Column | Type |
|---|---|
| activation_id | int |
| merchant_id | int |
| method | varchar |
| activated_on | date |
| deactivated_on | date |
Primary key: activation_id.
One row per time a method was switched on for a merchant; deactivated_on is the day it was switched off (it is not live that day), NULL while it is still on. A method can be switched on again later, as a new row.
Examples
Example 1
MethodRequest
| merchant_id | method |
|---|---|
| 1 | card |
| 1 | upi |
| 2 | card |
| 2 | emi |
| 2 | upi |
| 3 | upi |
| 4 | netbanking |
MethodActivation
| activation_id | merchant_id | method | activated_on | deactivated_on |
|---|---|---|---|---|
| 1 | 1 | upi | 2025-01-10 | NULL |
| 2 | 1 | card | 2025-01-10 | 2025-02-01 |
| 3 | 1 | card | 2025-02-15 | NULL |
| 4 | 2 | upi | 2025-01-12 | NULL |
| 5 | 2 | card | 2025-01-12 | 2025-03-31 |
| 6 | 2 | paylater | 2025-03-01 | NULL |
| 7 | 2 | emi | 2025-04-01 | NULL |
| 8 | 3 | upi | 2025-02-02 | NULL |
| 9 | 3 | wallet | 2025-02-02 | NULL |
| 10 | 3 | wallet | 2025-03-05 | NULL |
| 11 | 5 | upi | 2025-03-20 | NULL |
Output
| merchant_id | missing_methods | extra_methods |
|---|---|---|
| 2 | card,emi | paylater |
| 3 | NULL | wallet |
| 4 | netbanking | NULL |
| 5 | NULL | upi |
How to solve Razorpay: Merchants Whose Live Methods Differ From Their Request
Two sets are equal when each has nothing the other lacks, so the audit is two set differences. Start by turning the activation history into the set of methods live on the audit day: an activation counts when it began on or before 2025-03-31 and either was never switched off or was switched off after that day — deactivated_on = '2025-03-31' means it was off that day. DISTINCT collapses a method that has two live rows, so it can never be listed twice.
Missing methods are requested rows with no live partner, and extra methods are live rows with no request, each an anti join with NOT EXISTS. Stacking both with UNION ALL and a side label gives one row per discrepancy; a merchant whose sets match produces no rows at all and so drops out of the answer without any further test. That also covers merchants found in only one table — a merchant with live methods but no request has only extras, and one that asked for methods but has nothing live has only missing ones.
Per merchant, GROUP_CONCAT(CASE WHEN side = 'missing' THEN method END ORDER BY method SEPARATOR ',') lists one side alphabetically: the CASE yields NULL for the other side's rows, GROUP_CONCAT ignores NULLs, and when every value is NULL the result is NULL — the required empty value.
The alternative emulates a full outer join (MySQL has none): the union of both key sets, left-joined to each side, so a NULL on either side marks a difference, and HAVING drops merchants without one. Both read each table once with indexes on (merchant_id, method).
Reference solution (MySQL)
WITH live AS (
SELECT DISTINCT merchant_id, method
FROM MethodActivation
WHERE activated_on <= '2025-03-31'
AND (deactivated_on IS NULL OR deactivated_on > '2025-03-31')
), diff AS (
SELECT r.merchant_id, r.method, 'missing' AS side
FROM MethodRequest r
WHERE NOT EXISTS (SELECT 1 FROM live l WHERE l.merchant_id = r.merchant_id AND l.method = r.method)
UNION ALL
SELECT l.merchant_id, l.method, 'extra'
FROM live l
WHERE NOT EXISTS (SELECT 1 FROM MethodRequest r WHERE r.merchant_id = l.merchant_id AND r.method = l.method)
)
SELECT merchant_id,
GROUP_CONCAT(CASE WHEN side = 'missing' THEN method END ORDER BY method SEPARATOR ',') AS missing_methods,
GROUP_CONCAT(CASE WHEN side = 'extra' THEN method END ORDER BY method SEPARATOR ',') AS extra_methods
FROM diff
GROUP BY merchant_idAnother way
WITH live AS (
SELECT merchant_id, method
FROM MethodActivation
WHERE activated_on <= '2025-03-31' AND COALESCE(deactivated_on, '9999-12-31') > '2025-03-31'
GROUP BY merchant_id, method
), keyset AS (
SELECT merchant_id, method FROM MethodRequest
UNION
SELECT merchant_id, method FROM live
), joined AS (
SELECT k.merchant_id, k.method, r.method AS asked, l.method AS on_now
FROM keyset k
LEFT JOIN MethodRequest r ON r.merchant_id = k.merchant_id AND r.method = k.method
LEFT JOIN live l ON l.merchant_id = k.merchant_id AND l.method = k.method
)
SELECT merchant_id,
GROUP_CONCAT(IF(on_now IS NULL, method, NULL) ORDER BY method SEPARATOR ',') AS missing_methods,
GROUP_CONCAT(IF(asked IS NULL, method, NULL) ORDER BY method SEPARATOR ',') AS extra_methods
FROM joined
GROUP BY merchant_id
HAVING SUM(asked IS NULL OR on_now IS NULL) > 0← Razorpay: Payments Settled After Their T+N Working Day · Razorpay: Diwali-Week Captured Volume Against Last Year's Diwali Week →