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
| Column | Type |
|---|---|
| payment_id | int |
| method | varchar |
| amount | int |
Primary key: payment_id.
Captured payments, amount in rupees.
Table: PricingRule
| Column | Type |
|---|---|
| method | varchar |
| fee_percent | decimal |
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_id | method | amount |
|---|---|---|
| 1 | card | 1000 |
| 2 | netbanking | 555 |
| 3 | upi | 2000 |
| 4 | emi | 750 |
| 5 | wallet | 1234 |
PricingRule
| method | fee_percent |
|---|---|
| card | 2 |
| netbanking | 1.8 |
| upi | 0 |
| wallet | 2.4 |
Output
| payment_id | fee | settled_amount |
|---|---|---|
| 1 | 20 | 980 |
| 2 | 9.99 | 545.01 |
| 3 | 0 | 2000 |
| 4 | 0 | 750 |
| 5 | 29.62 | 1204.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.methodAnother 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 →