Razorpay: Merchants Whose Live Methods Differ From Their Request

Hard
917.

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

ColumnType
merchant_idint
methodvarchar

Primary key (together): merchant_id, method.

The methods each merchant asked for on its onboarding form. Method names are lower case.

Table: MethodActivation

ColumnType
activation_idint
merchant_idint
methodvarchar
activated_ondate
deactivated_ondate

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.

Example

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

MethodRequest

merchant_idmethod
1card
1upi
2card
2emi
2upi
3upi
4netbanking

MethodActivation

activation_idmerchant_idmethodactivated_ondeactivated_on
11upi2025-01-10NULL
21card2025-01-102025-02-01
31card2025-02-15NULL
42upi2025-01-12NULL
52card2025-01-122025-03-31
62paylater2025-03-01NULL
72emi2025-04-01NULL
83upi2025-02-02NULL
93wallet2025-02-02NULL
103wallet2025-03-05NULL
1 more row

Output

merchant_idmissing_methodsextra_methods
2card,emipaylater
3NULLwallet
4netbankingNULL
5NULLupi
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: 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

ColumnType
merchant_idint
methodvarchar

Primary key: merchant_id, method.

The methods each merchant asked for on its onboarding form. Method names are lower case.

Table: MethodActivation

ColumnType
activation_idint
merchant_idint
methodvarchar
activated_ondate
deactivated_ondate

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_idmethod
1card
1upi
2card
2emi
2upi
3upi
4netbanking

MethodActivation

activation_idmerchant_idmethodactivated_ondeactivated_on
11upi2025-01-10NULL
21card2025-01-102025-02-01
31card2025-02-15NULL
42upi2025-01-12NULL
52card2025-01-122025-03-31
62paylater2025-03-01NULL
72emi2025-04-01NULL
83upi2025-02-02NULL
93wallet2025-02-02NULL
103wallet2025-03-05NULL
115upi2025-03-20NULL

Output

merchant_idmissing_methodsextra_methods
2card,emipaylater
3NULLwallet
4netbankingNULL
5NULLupi

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_id

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