Razorpay: Leading Card Network in Each Merchant Category

Medium
910.

Razorpay: Leading Card Network in Each Merchant Category

Razorpay's partnerships team negotiates network incentives per vertical, so they want to know which card network carries the most captured money in each merchant category.

For every merchant category, return the card network(s) with the highest total captured amount in that category: category, card_network and captured_amount. If several networks tie for the top, return all of them. Only captured payments count, and categories with no captured card payment are not reported. 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
categoryvarchar

Primary key: merchant_id.

One row per merchant and its business category.

Table: CardPayment

ColumnTypeValues
payment_idint
merchant_idint
card_networkvarchar
amountint
statusenumcaptured, failed

Primary key: payment_id.

One row per card payment attempt, amount in rupees.

Example

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

Merchant

merchant_idcategory
1travel
2travel
3food
4education

CardPayment

payment_idmerchant_idcard_networkamountstatus
101Visa12000captured
112Mastercard9000captured
122Mastercard4000captured
131Amex30000failed
143RuPay500captured
153Visa500captured
164Visa7000failed

Output

categorycard_networkcaptured_amount
foodRuPay500
foodVisa500
travelMastercard13000
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: Leading Card Network in Each Merchant Category

Razorpay's partnerships team negotiates network incentives per vertical, so they want to know which card network carries the most captured money in each…

  • Difficulty: Medium
  • Topics: Window Functions, Joins, Aggregation
  • Dialect: MySQL
  • Problem: #910

Problem statement

Razorpay's partnerships team negotiates network incentives per vertical, so they want to know which card network carries the most captured money in each merchant category.

For every merchant category, return the card network(s) with the highest total captured amount in that category: category, card_network and captured_amount. If several networks tie for the top, return all of them. Only captured payments count, and categories with no captured card payment are not reported. 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
categoryvarchar

Primary key: merchant_id.

One row per merchant and its business category.

Table: CardPayment

ColumnType
payment_idint
merchant_idint
card_networkvarchar
amountint
statusenum(captured, failed)

Primary key: payment_id.

One row per card payment attempt, amount in rupees.

Examples

Example 1

Merchant

merchant_idcategory
1travel
2travel
3food
4education

CardPayment

payment_idmerchant_idcard_networkamountstatus
101Visa12000captured
112Mastercard9000captured
122Mastercard4000captured
131Amex30000failed
143RuPay500captured
153Visa500captured
164Visa7000failed

Output

categorycard_networkcaptured_amount
foodRuPay500
foodVisa500
travelMastercard13000

How to solve Razorpay: Leading Card Network in Each Merchant Category

The question has two steps. First, aggregate: join each card payment to its merchant to learn the category, keep captured payments, and sum the amount per (category, card_network). Second, pick the maximum within each category.

A window function does the second step neatly: RANK() OVER (PARTITION BY category ORDER BY captured_amount DESC) numbers the networks of each category from the largest total down, giving equal totals the same rank. Keeping rank 1 returns every network that ties for the top — the statement asks for all of them, which is why ROW_NUMBER() (one arbitrary winner) would be wrong. (The alias avoids rank, which is a reserved word in MySQL.)

The alternatives express "largest in its category" without windows: a NOT EXISTS that no network in the same category has a larger total, or a join to the per-category MAX. All three aggregate once; the ranking or the comparison then runs on the much smaller table of totals.

Reference solution (MySQL)

WITH totals AS (
  SELECT m.category, c.card_network, SUM(c.amount) AS captured_amount
  FROM CardPayment c
  JOIN Merchant m ON m.merchant_id = c.merchant_id
  WHERE c.status = 'captured'
  GROUP BY m.category, c.card_network
), ranked AS (
  SELECT category, card_network, captured_amount,
         RANK() OVER (PARTITION BY category ORDER BY captured_amount DESC) AS rnk
  FROM totals
)
SELECT category, card_network, captured_amount FROM ranked WHERE rnk = 1

Another way

WITH totals AS (
  SELECT m.category, c.card_network, SUM(c.amount) AS captured_amount
  FROM CardPayment c JOIN Merchant m ON m.merchant_id = c.merchant_id
  WHERE c.status = 'captured' GROUP BY m.category, c.card_network
)
SELECT t.category, t.card_network, t.captured_amount FROM totals t
WHERE NOT EXISTS (SELECT 1 FROM totals u WHERE u.category = t.category AND u.captured_amount > t.captured_amount)

Another way

WITH totals AS (
  SELECT m.category, c.card_network, SUM(c.amount) AS captured_amount
  FROM CardPayment c JOIN Merchant m ON m.merchant_id = c.merchant_id
  WHERE c.status = 'captured' GROUP BY m.category, c.card_network
)
SELECT t.category, t.card_network, t.captured_amount FROM totals t
JOIN (SELECT category, MAX(captured_amount) AS top FROM totals GROUP BY category) b
  ON b.category = t.category AND b.top = t.captured_amount

← Razorpay: Subscription Cycles Recovered by Retry vs Halted · Razorpay: Expense Claims Not Approved by the Claimant's Manager →