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
| Column | Type |
|---|---|
| merchant_id | int |
| category | varchar |
Primary key: merchant_id.
One row per merchant and its business category.
Table: CardPayment
| Column | Type |
|---|---|
| payment_id | int |
| merchant_id | int |
| card_network | varchar |
| amount | int |
| status | enum(captured, failed) |
Primary key: payment_id.
One row per card payment attempt, amount in rupees.
Examples
Example 1
Merchant
| merchant_id | category |
|---|---|
| 1 | travel |
| 2 | travel |
| 3 | food |
| 4 | education |
CardPayment
| payment_id | merchant_id | card_network | amount | status |
|---|---|---|---|---|
| 10 | 1 | Visa | 12000 | captured |
| 11 | 2 | Mastercard | 9000 | captured |
| 12 | 2 | Mastercard | 4000 | captured |
| 13 | 1 | Amex | 30000 | failed |
| 14 | 3 | RuPay | 500 | captured |
| 15 | 3 | Visa | 500 | captured |
| 16 | 4 | Visa | 7000 | failed |
Output
| category | card_network | captured_amount |
|---|---|---|
| food | RuPay | 500 |
| food | Visa | 500 |
| travel | Mastercard | 13000 |
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 = 1Another 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 →