Razorpay: Payments Settled After Their T+N Working Day

Hard
916.

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

ColumnType
merchant_idint
business_namevarchar
settlement_cycleint

Primary key: merchant_id.

settlement_cycle is N in T+N (1, 2 or 3).

Table: Payment

ColumnType
payment_idint
merchant_idint
captured_ondate
settled_ondate

Primary key: payment_id.

Captured payments; settled_on is NULL until the money reaches the merchant.

Table: WorkingDay

ColumnType
work_datedate

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.

Example

The rows must come back in the order shown.

Merchant

merchant_idbusiness_namesettlement_cycle
1Kirana Kart1
2TrekNest2
3BookNook3

Payment

payment_idmerchant_idcaptured_onsettled_on
1012025-03-062025-03-07
1112025-03-072025-03-11
1222025-03-072025-03-12
1322025-03-122025-03-17
1432025-03-13NULL
1512025-03-28NULL

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
9 more rows

Output

payment_idmerchant_idcaptured_ondue_onsettled_on
1112025-03-072025-03-102025-03-11
1222025-03-072025-03-112025-03-12
1432025-03-132025-03-19NULL
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: 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

ColumnType
merchant_idint
business_namevarchar
settlement_cycleint

Primary key: merchant_id.

settlement_cycle is N in T+N (1, 2 or 3).

Table: Payment

ColumnType
payment_idint
merchant_idint
captured_ondate
settled_ondate

Primary key: payment_id.

Captured payments; settled_on is NULL until the money reaches the merchant.

Table: WorkingDay

ColumnType
work_datedate

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_idbusiness_namesettlement_cycle
1Kirana Kart1
2TrekNest2
3BookNook3

Payment

payment_idmerchant_idcaptured_onsettled_on
1012025-03-062025-03-07
1112025-03-072025-03-11
1222025-03-072025-03-12
1322025-03-122025-03-17
1432025-03-13NULL
1512025-03-28NULL

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_idmerchant_idcaptured_ondue_onsettled_on
1112025-03-072025-03-102025-03-11
1222025-03-072025-03-112025-03-12
1432025-03-132025-03-19NULL

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_id

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