Razorpay: Settlements That Don't Match Their Line Items

Medium
913.

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

ColumnType
settlement_idint
merchant_idint
settled_ondate
amountint

Primary key: settlement_id.

One row per bank credit made to a merchant.

Table: SettlementLine

ColumnTypeValues
line_idint
settlement_idint
line_typeenumpayment, refund, fee, tax
amountint

Primary key: line_id.

The entries a settlement is made of. amount is always positive; line_type says whether it adds or deducts.

Example

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

Settlement

settlement_idmerchant_idsettled_onamount
16012025-03-049620
26012025-03-054000
36022025-03-052950
46032025-03-060
56032025-03-071500

SettlementLine

line_idsettlement_idline_typeamount
11payment10000
21fee200
31tax36
41refund144
52payment4100
62fee82
73payment3000
83fee50

Output

settlement_idmerchant_idexpected_amountsettled_amountdifference
260140184000-18
5603015001500
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: 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

ColumnType
settlement_idint
merchant_idint
settled_ondate
amountint

Primary key: settlement_id.

One row per bank credit made to a merchant.

Table: SettlementLine

ColumnType
line_idint
settlement_idint
line_typeenum(payment, refund, fee, tax)
amountint

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_idmerchant_idsettled_onamount
16012025-03-049620
26012025-03-054000
36022025-03-052950
46032025-03-060
56032025-03-071500

SettlementLine

line_idsettlement_idline_typeamount
11payment10000
21fee200
31tax36
41refund144
52payment4100
62fee82
73payment3000
83fee50

Output

settlement_idmerchant_idexpected_amountsettled_amountdifference
260140184000-18
5603015001500

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 →