Razorpay: Settlements That Don't Match Their Line Items
The reconciliation team at Razorpay checks that every settlement credited to a merchant equals what its line items add up to: payments, minus refunds, minus…
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #913
Problem statement
The reconciliation team at Razorpay checks that every settlement credited to a merchant equals what its line items add up to: payments, minus refunds, minus gateway fees, minus tax on fees.
For each settlement, expected_amount = sum of payment lines − sum of refund, fee and tax lines (a settlement with no lines expects 0). Return the settlements whose amount differs from that: settlement_id, merchant_id, expected_amount, settled_amount (the settlement's amount) and difference = settled − expected. 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: Settlement
| Column | Type |
|---|---|
| settlement_id | int |
| merchant_id | int |
| settled_on | date |
| amount | int |
Primary key: settlement_id.
One row per bank credit made to a merchant.
Table: SettlementLine
| Column | Type |
|---|---|
| line_id | int |
| settlement_id | int |
| line_type | enum(payment, refund, fee, tax) |
| amount | int |
Primary key: line_id.
The entries a settlement is made of. amount is always positive; line_type says whether it adds or deducts.
Examples
Example 1
Settlement
| settlement_id | merchant_id | settled_on | amount |
|---|---|---|---|
| 1 | 601 | 2025-03-04 | 9620 |
| 2 | 601 | 2025-03-05 | 4000 |
| 3 | 602 | 2025-03-05 | 2950 |
| 4 | 603 | 2025-03-06 | 0 |
| 5 | 603 | 2025-03-07 | 1500 |
SettlementLine
| line_id | settlement_id | line_type | amount |
|---|---|---|---|
| 1 | 1 | payment | 10000 |
| 2 | 1 | fee | 200 |
| 3 | 1 | tax | 36 |
| 4 | 1 | refund | 144 |
| 5 | 2 | payment | 4100 |
| 6 | 2 | fee | 82 |
| 7 | 3 | payment | 3000 |
| 8 | 3 | fee | 50 |
Output
| settlement_id | merchant_id | expected_amount | settled_amount | difference |
|---|---|---|---|---|
| 2 | 601 | 4018 | 4000 | -18 |
| 5 | 603 | 0 | 1500 | 1500 |
How to solve Razorpay: Settlements That Don't Match Their Line Items
Each settlement's expected amount is a signed sum of its lines, so join the lines and group by settlement. A CASE gives each line its sign — payment lines count positive, refund, fee and tax lines negative — and SUM adds them up.
A settlement with no lines is still a bank credit that must be explained; the LEFT JOIN keeps it as one row with NULL line columns. SUM over only NULLs is NULL, so COALESCE(…, 0) makes its expected amount 0 — and a non-zero credit with nothing behind it is reported, while a zero one is not. The settlement's own amount is in the GROUP BY, so it can be selected and compared in HAVING.
The alternative computes the expected amount with a correlated scalar subquery per settlement (payments minus deductions, each IFNULL-ed) and filters in an outer query, which avoids repeating the expression three times. Both read every line once with an index on SettlementLine(settlement_id).
Reference solution (MySQL)
SELECT s.settlement_id, s.merchant_id,
COALESCE(SUM(CASE WHEN l.line_type = 'payment' THEN l.amount ELSE -l.amount END), 0) AS expected_amount,
s.amount AS settled_amount,
s.amount - COALESCE(SUM(CASE WHEN l.line_type = 'payment' THEN l.amount ELSE -l.amount END), 0) AS difference
FROM Settlement s
LEFT JOIN SettlementLine l ON l.settlement_id = s.settlement_id
GROUP BY s.settlement_id, s.merchant_id, s.amount
HAVING s.amount <> COALESCE(SUM(CASE WHEN l.line_type = 'payment' THEN l.amount ELSE -l.amount END), 0)Another way
SELECT settlement_id, merchant_id, expected_amount, settled_amount, settled_amount - expected_amount AS difference
FROM (SELECT s.settlement_id, s.merchant_id, s.amount AS settled_amount,
(SELECT IFNULL(SUM(IF(l.line_type = 'payment', l.amount, 0)), 0) - IFNULL(SUM(IF(l.line_type <> 'payment', l.amount, 0)), 0)
FROM SettlementLine l WHERE l.settlement_id = s.settlement_id) AS expected_amount
FROM Settlement s) t
WHERE settled_amount <> expected_amount← Razorpay: UPI Ticket Sizes in ₹1,000 Bands, Empty Bands Included · Razorpay: MDR Pricing Rows Added, Removed or Changed Between Snapshots →