Razorpay: Gateway Fee and Settled Amount for Each Payment

Easy
902.

Razorpay: Gateway Fee and Settled Amount for Each Payment

EasyJoinsBasics

MySQL

A settlements analyst at Razorpay is checking how much of each payment a merchant should receive after the gateway fee (MDR). The fee depends only on the payment method, from a pricing table.

For every payment return payment_id, fee = amount × fee_percent / 100 rounded to 2 decimals, and settled_amount = amount − fee. A method that has no row in PricingRule is charged nothing, so its fee is 0. 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
methodvarchar
amountint

Primary key: payment_id.

Captured payments, amount in rupees.

Table: PricingRule

ColumnType
methodvarchar
fee_percentdecimal

Primary key: method.

The merchant's fee per method, as a percentage (2.00 means 2%). UPI can be priced at 0.

Example

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

Payment

payment_idmethodamount
1card1000
2netbanking555
3upi2000
4emi750
5wallet1234

PricingRule

methodfee_percent
card2
netbanking1.8
upi0
wallet2.4

Output

payment_idfeesettled_amount
120980
29.99545.01
302000
40750
529.621204.38
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: Gateway Fee and Settled Amount for Each Payment

A settlements analyst at Razorpay is checking how much of each payment a merchant should receive after the gateway fee (MDR).

  • Difficulty: Easy
  • Topics: Joins, Basics
  • Dialect: MySQL
  • Problem: #902

Problem statement

A settlements analyst at Razorpay is checking how much of each payment a merchant should receive after the gateway fee (MDR). The fee depends only on the payment method, from a pricing table.

For every payment return payment_id, fee = amount × fee_percent / 100 rounded to 2 decimals, and settled_amount = amount − fee. A method that has no row in PricingRule is charged nothing, so its fee is 0. 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
methodvarchar
amountint

Primary key: payment_id.

Captured payments, amount in rupees.

Table: PricingRule

ColumnType
methodvarchar
fee_percentdecimal

Primary key: method.

The merchant's fee per method, as a percentage (2.00 means 2%). UPI can be priced at 0.

Examples

Example 1

Payment

payment_idmethodamount
1card1000
2netbanking555
3upi2000
4emi750
5wallet1234

PricingRule

methodfee_percent
card2
netbanking1.8
upi0
wallet2.4

Output

payment_idfeesettled_amount
120980
29.99545.01
302000
40750
529.621204.38

How to solve Razorpay: Gateway Fee and Settled Amount for Each Payment

Each payment needs the fee percentage of its method, so join Payment to PricingRule on method. It must be a LEFT JOIN: an inner join would silently drop the EMI payment whose method has no rule, while the statement says such payments are charged nothing and still belong in the answer.

For a payment without a rule, r.fee_percent is NULL, so the whole fee expression is NULL. COALESCE(…, 0) turns it into the 0 the statement asks for — and it must happen before the subtraction, because amount - NULL is NULL as well. Round the fee to paise first and subtract the rounded value, which is how a settlement statement would show it.

The alternative looks the fee up with a correlated scalar subquery wrapped in IFNULL, then computes the settled amount in an outer query from the derived column. Both read each payment once and find its rule through the primary key on method.

Reference solution (MySQL)

SELECT p.payment_id,
       COALESCE(ROUND(p.amount * r.fee_percent / 100, 2), 0) AS fee,
       p.amount - COALESCE(ROUND(p.amount * r.fee_percent / 100, 2), 0) AS settled_amount
FROM Payment p
LEFT JOIN PricingRule r ON r.method = p.method

Another way

SELECT payment_id, fee, amount - fee AS settled_amount
FROM (SELECT p.payment_id, p.amount,
             IFNULL((SELECT ROUND(p.amount * r.fee_percent / 100, 2) FROM PricingRule r WHERE r.method = p.method), 0) AS fee
      FROM Payment p) t

← Razorpay: Open QR Codes That Never Received a Payment · Razorpay: Normal-Speed Refunds That Took Over a Week →