Razorpay: Captured Volume by Payment Method per Merchant

Easy
899.

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

ColumnTypeValues
payment_idint
merchant_idint
methodenumupi, card, netbanking, wallet, emi
amountint
statusenumcaptured, authorized, failed

Primary key: payment_id.

One row per payment attempt, amount in rupees. Only captured payments have moved money to the merchant.

Example

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

Payment

payment_idmerchant_idmethodamountstatus
1101upi450captured
2101card2200captured
3101upi300captured
4101wallet150failed
5102emi18000captured
6102wallet600captured
7103card999authorized
8102netbanking4100captured

Output

merchant_idupi_amountcard_amountnetbanking_amountother_amount
101750220000
10200410018600
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: 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

ColumnType
payment_idint
merchant_idint
methodenum(upi, card, netbanking, wallet, emi)
amountint
statusenum(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_idmerchant_idmethodamountstatus
1101upi450captured
2101card2200captured
3101upi300captured
4101wallet150failed
5102emi18000captured
6102wallet600captured
7103card999authorized
8102netbanking4100captured

Output

merchant_idupi_amountcard_amountnetbanking_amountother_amount
101750220000
10200410018600

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_id

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

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