Razorpay: Bank Credits That Match No Settlement

Medium
915.

Razorpay: Bank Credits That Match No Settlement

MediumStringsJoins

MySQL

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

ColumnType
settlement_idvarchar
merchant_idint
amountint
utrvarchar

Primary key: settlement_id.

Settlements Razorpay reports for the merchant; ids look like setl_40211.

Table: BankStatementLine

ColumnType
line_idint
credited_ondate
narrationvarchar
amountint

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

Example

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

Settlement

settlement_idmerchant_idamountutr
setl_4021170118450AXN5039921
setl_402127019200AXN5039958
setl_402307015000AXN5040102

BankStatementLine

line_idcredited_onnarrationamount
12025-03-03RAZORPAY/setl_40211/UTR/AXN503992118450
22025-03-04RAZORPAY/setl_40212/UTR/AXN50399589020
32025-03-04NEFT/ACME TRADERS/INV-223112000
42025-03-05RAZORPAY/setl_40299/UTR/AXN50403777600
52025-03-05RAZORPAYX/pout_88120/REVERSAL2500
62025-03-06RAZORPAY/setl_40230/UTR/AXN50401025000

Output

line_idsettlement_refcredited_amountexpected_amount
2setl_4021290209200
4setl_402997600NULL
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: 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

ColumnType
settlement_idvarchar
merchant_idint
amountint
utrvarchar

Primary key: settlement_id.

Settlements Razorpay reports for the merchant; ids look like setl_40211.

Table: BankStatementLine

ColumnType
line_idint
credited_ondate
narrationvarchar
amountint

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_idmerchant_idamountutr
setl_4021170118450AXN5039921
setl_402127019200AXN5039958
setl_402307015000AXN5040102

BankStatementLine

line_idcredited_onnarrationamount
12025-03-03RAZORPAY/setl_40211/UTR/AXN503992118450
22025-03-04RAZORPAY/setl_40212/UTR/AXN50399589020
32025-03-04NEFT/ACME TRADERS/INV-223112000
42025-03-05RAZORPAY/setl_40299/UTR/AXN50403777600
52025-03-05RAZORPAYX/pout_88120/REVERSAL2500
62025-03-06RAZORPAY/setl_40230/UTR/AXN50401025000

Output

line_idsettlement_refcredited_amountexpected_amount
2setl_4021290209200
4setl_402997600NULL

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

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