Razorpay: Bank Credits That Match No Settlement
A merchant's finance team reconciles their bank statement against Razorpay settlements.
- Difficulty: Medium
- Topics: Strings, Joins
- Dialect: MySQL
- Problem: #915
Problem statement
A merchant's finance team reconciles their bank statement against Razorpay settlements. Every Razorpay credit carries a narration like RAZORPAY/setl_40211/UTR/AXN5039921: the settlement id sits between the first and second /.
Among statement lines whose narration starts with RAZORPAY/ (lines from other senders, including RAZORPAYX/… payouts, are ignored), return those whose settlement id has no row in Settlement or whose credited amount differs from the settlement's amount: line_id, settlement_ref (the extracted id), credited_amount (the line's amount) and expected_amount (the settlement's amount, NULL when there is none). 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 | varchar |
| merchant_id | int |
| amount | int |
| utr | varchar |
Primary key: settlement_id.
Settlements Razorpay reports for the merchant; ids look like setl_40211.
Table: BankStatementLine
| Column | Type |
|---|---|
| line_id | int |
| credited_on | date |
| narration | varchar |
| amount | int |
Primary key: line_id.
One row per credit on the merchant's bank statement. A Razorpay credit's narration is RAZORPAY/<settlement id>/UTR/<utr>.
Examples
Example 1
Settlement
| settlement_id | merchant_id | amount | utr |
|---|---|---|---|
| setl_40211 | 701 | 18450 | AXN5039921 |
| setl_40212 | 701 | 9200 | AXN5039958 |
| setl_40230 | 701 | 5000 | AXN5040102 |
BankStatementLine
| line_id | credited_on | narration | amount |
|---|---|---|---|
| 1 | 2025-03-03 | RAZORPAY/setl_40211/UTR/AXN5039921 | 18450 |
| 2 | 2025-03-04 | RAZORPAY/setl_40212/UTR/AXN5039958 | 9020 |
| 3 | 2025-03-04 | NEFT/ACME TRADERS/INV-2231 | 12000 |
| 4 | 2025-03-05 | RAZORPAY/setl_40299/UTR/AXN5040377 | 7600 |
| 5 | 2025-03-05 | RAZORPAYX/pout_88120/REVERSAL | 2500 |
| 6 | 2025-03-06 | RAZORPAY/setl_40230/UTR/AXN5040102 | 5000 |
Output
| line_id | settlement_ref | credited_amount | expected_amount |
|---|---|---|---|
| 2 | setl_40212 | 9020 | 9200 |
| 4 | setl_40299 | 7600 | NULL |
How to solve Razorpay: Bank Credits That Match No Settlement
Two steps: parse, then reconcile. The narration has a fixed shape, so the settlement id is the second /-separated field. SUBSTRING_INDEX(narration, '/', 2) returns RAZORPAY/setl_40211, and SUBSTRING_INDEX(…, '/', -1) keeps the part after its last slash, setl_40211. The prefix test LIKE 'RAZORPAY/%' includes the slash on purpose, so RazorpayX payout reversals (RAZORPAYX/…) and other senders are not mistaken for settlements.
Then LEFT JOIN the parsed lines to Settlement. A line whose id is unknown gets NULL settlement columns and is reported with a NULL expected_amount; a line that matched is reported only when the amounts differ. Note that s.amount <> rz.amount alone would miss the unknown ids, because a comparison with NULL is never true — the IS NULL branch is needed.
The alternative cuts the id with SUBSTRING and LOCATE('/UTR/'), and states the rule as "no settlement with this id and this amount" using NOT EXISTS, looking the expected amount up with a scalar subquery. Both scan the statement once and probe Settlement through its primary key.
Reference solution (MySQL)
WITH rz AS (
SELECT line_id, SUBSTRING_INDEX(SUBSTRING_INDEX(narration, '/', 2), '/', -1) AS settlement_ref, amount
FROM BankStatementLine
WHERE narration LIKE 'RAZORPAY/%'
)
SELECT rz.line_id, rz.settlement_ref, rz.amount AS credited_amount, s.amount AS expected_amount
FROM rz
LEFT JOIN Settlement s ON s.settlement_id = rz.settlement_ref
WHERE s.settlement_id IS NULL OR s.amount <> rz.amountAnother way
SELECT line_id, settlement_ref, credited_amount,
(SELECT s.amount FROM Settlement s WHERE s.settlement_id = t.settlement_ref) AS expected_amount
FROM (SELECT line_id, SUBSTRING(narration, 10, LOCATE('/UTR/', narration) - 10) AS settlement_ref, amount AS credited_amount
FROM BankStatementLine WHERE LEFT(narration, 9) = 'RAZORPAY/') t
WHERE NOT EXISTS (SELECT 1 FROM Settlement s WHERE s.settlement_id = t.settlement_ref AND s.amount = t.credited_amount)← Razorpay: MDR Pricing Rows Added, Removed or Changed Between Snapshots · Razorpay: Payments Settled After Their T+N Working Day →