Razorpay: Captured Volume by Payment Method per Merchant
A merchant's account manager at Razorpay wants one row per merchant showing how its captured money splits across payment methods, to pitch the right offers…
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #899
Problem statement
A merchant's account manager at Razorpay wants one row per merchant showing how its captured money splits across payment methods, to pitch the right offers (EMI, UPI Autopay, cards).
Consider only payments with status captured. For every merchant with at least one captured payment, return merchant_id, upi_amount, card_amount, netbanking_amount and other_amount (wallet and EMI together) — the total captured amount in each bucket, 0 when the merchant has none in it. Return the rows in 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: Payment
| Column | Type |
|---|---|
| payment_id | int |
| merchant_id | int |
| method | enum(upi, card, netbanking, wallet, emi) |
| amount | int |
| status | enum(captured, authorized, failed) |
Primary key: payment_id.
One row per payment attempt, amount in rupees. Only captured payments have moved money to the merchant.
Examples
Example 1
Payment
| payment_id | merchant_id | method | amount | status |
|---|---|---|---|---|
| 1 | 101 | upi | 450 | captured |
| 2 | 101 | card | 2200 | captured |
| 3 | 101 | upi | 300 | captured |
| 4 | 101 | wallet | 150 | failed |
| 5 | 102 | emi | 18000 | captured |
| 6 | 102 | wallet | 600 | captured |
| 7 | 103 | card | 999 | authorized |
| 8 | 102 | netbanking | 4100 | captured |
Output
| merchant_id | upi_amount | card_amount | netbanking_amount | other_amount |
|---|---|---|---|---|
| 101 | 750 | 2200 | 0 | 0 |
| 102 | 0 | 0 | 4100 | 18600 |
How to solve Razorpay: Captured Volume by Payment Method per Merchant
This is a pivot: rows of (merchant, method, amount) become one row per merchant with a column per method bucket. The tool for it is conditional aggregation — group by merchant_id, and inside each SUM let a CASE pass the amount through only when the row belongs to that column's bucket and 0 otherwise.
The ELSE 0 matters: without it a merchant with no card payments would get NULL in card_amount, while the statement asks for 0. Wallet and EMI share one bucket, so their test is method IN ('wallet', 'emi').
The status filter goes in WHERE, so failed and merely authorized attempts never reach the groups, and a merchant whose only payments failed forms no group at all and is left out. The alternatives use IF, a subtraction for the last bucket, and MySQL's habit of treating a comparison as 1 or 0 (amount * (method = 'upi')). All are a single pass over the table.
Reference solution (MySQL)
SELECT merchant_id,
SUM(CASE WHEN method = 'upi' THEN amount ELSE 0 END) AS upi_amount,
SUM(CASE WHEN method = 'card' THEN amount ELSE 0 END) AS card_amount,
SUM(CASE WHEN method = 'netbanking' THEN amount ELSE 0 END) AS netbanking_amount,
SUM(CASE WHEN method IN ('wallet', 'emi') THEN amount ELSE 0 END) AS other_amount
FROM Payment
WHERE status = 'captured'
GROUP BY merchant_idAnother way
SELECT merchant_id,
SUM(IF(method = 'upi', amount, 0)) AS upi_amount,
SUM(IF(method = 'card', amount, 0)) AS card_amount,
SUM(IF(method = 'netbanking', amount, 0)) AS netbanking_amount,
SUM(amount) - SUM(IF(method IN ('upi', 'card', 'netbanking'), amount, 0)) AS other_amount
FROM Payment WHERE status = 'captured' GROUP BY merchant_idAnother way
SELECT merchant_id, SUM(amount * (method = 'upi')) AS upi_amount, SUM(amount * (method = 'card')) AS card_amount,
SUM(amount * (method = 'netbanking')) AS netbanking_amount, SUM(amount * (method = 'wallet' OR method = 'emi')) AS other_amount
FROM Payment WHERE status = 'captured' GROUP BY merchant_id← Razorpay: Payment Links That Lapsed Without Payment · Razorpay: Merchants Whose KYC Documents Are All Unverified →