Razorpay: Payments Settled After Their T+N Working Day
Razorpay settles a captured payment on a merchant's T+N cycle: N working days after the capture date, where only days listed in the bank calendar count.
- Difficulty: Hard
- Topics: Window Functions, Dates, Subqueries
- Dialect: MySQL
- Problem: #916
Problem statement
Razorpay settles a captured payment on a merchant's T+N cycle: N working days after the capture date, where only days listed in the bank calendar count. The settlements ops team wants the payments that missed their date.
A payment's due_on is the N-th working day strictly after captured_on, with N = the merchant's settlement_cycle (so T+1 is the first working day after capture). Report every payment that was settled later than due_on, or is still unsettled (settled_on NULL) with due_on before 2025-03-31. Return payment_id, merchant_id, captured_on, due_on and settled_on, ordered by due_on, then payment_id.
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: Merchant
| Column | Type |
|---|---|
| merchant_id | int |
| business_name | varchar |
| settlement_cycle | int |
Primary key: merchant_id.
settlement_cycle is N in T+N (1, 2 or 3).
Table: Payment
| Column | Type |
|---|---|
| payment_id | int |
| merchant_id | int |
| captured_on | date |
| settled_on | date |
Primary key: payment_id.
Captured payments; settled_on is NULL until the money reaches the merchant.
Table: WorkingDay
| Column | Type |
|---|---|
| work_date | date |
Primary key: work_date.
Every bank working day of the period, which always extends well past the last capture; weekends and bank holidays are absent.
Examples
Example 1
Merchant
| merchant_id | business_name | settlement_cycle |
|---|---|---|
| 1 | Kirana Kart | 1 |
| 2 | TrekNest | 2 |
| 3 | BookNook | 3 |
Payment
| payment_id | merchant_id | captured_on | settled_on |
|---|---|---|---|
| 10 | 1 | 2025-03-06 | 2025-03-07 |
| 11 | 1 | 2025-03-07 | 2025-03-11 |
| 12 | 2 | 2025-03-07 | 2025-03-12 |
| 13 | 2 | 2025-03-12 | 2025-03-17 |
| 14 | 3 | 2025-03-13 | NULL |
| 15 | 1 | 2025-03-28 | NULL |
WorkingDay
| work_date |
|---|
| 2025-03-06 |
| 2025-03-07 |
| 2025-03-10 |
| 2025-03-11 |
| 2025-03-12 |
| 2025-03-13 |
| 2025-03-17 |
| 2025-03-18 |
| 2025-03-19 |
| 2025-03-20 |
| 2025-03-21 |
| 2025-03-24 |
| 2025-03-25 |
| 2025-03-26 |
| 2025-03-27 |
| 2025-03-28 |
| 2025-04-01 |
| 2025-04-02 |
| 2025-04-03 |
Output
| payment_id | merchant_id | captured_on | due_on | settled_on |
|---|---|---|---|---|
| 11 | 1 | 2025-03-07 | 2025-03-10 | 2025-03-11 |
| 12 | 2 | 2025-03-07 | 2025-03-11 | 2025-03-12 |
| 14 | 3 | 2025-03-13 | 2025-03-19 | NULL |
How to solve Razorpay: Payments Settled After Their T+N Working Day
Adding days to a date cannot skip weekends and bank holidays, but the WorkingDay table already lists exactly the days that count. Number them with ROW_NUMBER() OVER (ORDER BY work_date) and the problem becomes integer arithmetic: if the first working day strictly after the capture has number k, the T+N due date is the working day numbered k + N − 1.
k is MIN(n) over working days later than captured_on — a capture on a Friday or a holiday still finds the next working day, and a capture on a working day correctly skips itself because the comparison is strict. Joining back to the numbered calendar on n = k + N − 1 turns the number into the date due_on.
Then report late payments: settled after due_on, or not settled at all with a due date before the report date. The second branch is needed because a NULL settled_on makes the first comparison unknown. The order is fixed by due_on and payment_id.
The alternative avoids the window: it joins each payment to every later working day and keeps the one for which exactly N working days lie in (captured_on, work_date]. That is a counting subquery per candidate pair — quadratic in the calendar — whereas numbering the calendar once makes each lookup a single index probe.
Reference solution (MySQL)
WITH wd AS (
SELECT work_date, ROW_NUMBER() OVER (ORDER BY work_date) AS n FROM WorkingDay
), due AS (
SELECT p.payment_id, p.merchant_id, p.captured_on, p.settled_on,
(SELECT MIN(w.n) FROM wd w WHERE w.work_date > p.captured_on) + m.settlement_cycle - 1 AS due_n
FROM Payment p
JOIN Merchant m ON m.merchant_id = p.merchant_id
)
SELECT d.payment_id, d.merchant_id, d.captured_on, w.work_date AS due_on, d.settled_on
FROM due d
JOIN wd w ON w.n = d.due_n
WHERE d.settled_on > w.work_date
OR (d.settled_on IS NULL AND w.work_date < '2025-03-31')
ORDER BY due_on, d.payment_idAnother way
SELECT p.payment_id, p.merchant_id, p.captured_on, w.work_date AS due_on, p.settled_on
FROM Payment p
JOIN Merchant m ON m.merchant_id = p.merchant_id
JOIN WorkingDay w ON w.work_date > p.captured_on
AND (SELECT COUNT(*) FROM WorkingDay x WHERE x.work_date > p.captured_on AND x.work_date <= w.work_date) = m.settlement_cycle
WHERE p.settled_on > w.work_date OR (p.settled_on IS NULL AND w.work_date < '2025-03-31')
ORDER BY w.work_date, p.payment_id← Razorpay: Bank Credits That Match No Settlement · Razorpay: Merchants Whose Live Methods Differ From Their Request →