Razorpay: Queued Payouts Rejected for Insufficient Balance

Hard
920.

Razorpay: Queued Payouts Rejected for Insufficient Balance

On a RazorpayX current account, queued payouts are released one by one when the balance is checked. The payouts team simulates the queue to tell a business which payouts will bounce.

Each account starts at its opening_balance. Its payouts are processed in queued_at order (ties by smaller payout_id). A payout goes through when its amount is at most the current balance, which then drops by the amount; otherwise it is rejected, the balance is unchanged, and processing continues with the next payout (a later, smaller one may still go through). Return every rejected payout: payout_id, account_id, amount and balance_at_attempt (the balance when it was tried), ordered by payout_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: CurrentAccount

ColumnType
account_idint
business_namevarchar
opening_balanceint

Primary key: account_id.

A RazorpayX current account and its balance before the queue is processed.

Table: QueuedPayout

ColumnType
payout_idint
account_idint
amountint
queued_atdatetime

Primary key: payout_id.

Payouts waiting for funds; account_id always names an account.

Example

The rows must come back in the order shown.

CurrentAccount

account_idbusiness_nameopening_balance
1Kirana Kart50000
2PixelPlay Studio20000
3BookNook10000

QueuedPayout

payout_idaccount_idamountqueued_at
1011300002025-03-10 09:00:00
1021250002025-03-10 09:05:00
1031150002025-03-10 09:10:00
104150002025-03-10 09:20:00
105110002025-03-10 09:30:00
1062200002025-03-10 10:00:00
107212025-03-10 10:00:00
108340002025-03-10 11:00:00

Output

payout_idaccount_idamountbalance_at_attempt
10212500020000
105110000
107210
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: Queued Payouts Rejected for Insufficient Balance

On a RazorpayX current account, queued payouts are released one by one when the balance is checked.

  • Difficulty: Hard
  • Topics: Subqueries, Window Functions
  • Dialect: MySQL
  • Problem: #920

Problem statement

On a RazorpayX current account, queued payouts are released one by one when the balance is checked. The payouts team simulates the queue to tell a business which payouts will bounce.

Each account starts at its opening_balance. Its payouts are processed in queued_at order (ties by smaller payout_id). A payout goes through when its amount is at most the current balance, which then drops by the amount; otherwise it is rejected, the balance is unchanged, and processing continues with the next payout (a later, smaller one may still go through). Return every rejected payout: payout_id, account_id, amount and balance_at_attempt (the balance when it was tried), ordered by payout_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: CurrentAccount

ColumnType
account_idint
business_namevarchar
opening_balanceint

Primary key: account_id.

A RazorpayX current account and its balance before the queue is processed.

Table: QueuedPayout

ColumnType
payout_idint
account_idint
amountint
queued_atdatetime

Primary key: payout_id.

Payouts waiting for funds; account_id always names an account.

Examples

Example 1

CurrentAccount

account_idbusiness_nameopening_balance
1Kirana Kart50000
2PixelPlay Studio20000
3BookNook10000

QueuedPayout

payout_idaccount_idamountqueued_at
1011300002025-03-10 09:00:00
1021250002025-03-10 09:05:00
1031150002025-03-10 09:10:00
104150002025-03-10 09:20:00
105110002025-03-10 09:30:00
1062200002025-03-10 10:00:00
107212025-03-10 10:00:00
108340002025-03-10 11:00:00

Output

payout_idaccount_idamountbalance_at_attempt
10212500020000
105110000
107210

How to solve Razorpay: Queued Payouts Rejected for Insufficient Balance

It is tempting to compare a running SUM of amounts with the opening balance, but that assumes every earlier payout went through. Here a rejected payout leaves the balance unchanged and a later, smaller payout can still fit, so whether payout n succeeds depends on every decision before it. That is a sequential simulation, and SQL's tool for it is a recursive CTE.

First number each account's payouts in queue order with ROW_NUMBER() (queue time, then payout_id). The anchor of the recursion takes payout 1 of every account and computes the balance after it: reduced by the amount if the amount is at most the opening balance, unchanged otherwise. Each recursive step joins the previous state to payout n + 1 of the same account and applies the same rule to the carried balance. Every row keeps bal_before, the balance at the moment of the attempt, so the final select just keeps amount > bal_before — the rejected payouts. A payout equal to the balance goes through.

The alternative recurses without numbering: the anchor is the payout with no earlier one on the account (NOT EXISTS), and each step finds the next payout as the later one with nothing in between. It is the same walk, but each step costs a correlated search instead of a lookup by number. The recursion depth is the longest queue; all accounts advance together.

Reference solution (MySQL)

WITH RECURSIVE q AS (
  SELECT payout_id, account_id, amount,
         ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY queued_at, payout_id) AS n
  FROM QueuedPayout
), sim AS (
  SELECT q.account_id, q.n, q.payout_id, q.amount, a.opening_balance AS bal_before,
         CASE WHEN q.amount <= a.opening_balance THEN a.opening_balance - q.amount ELSE a.opening_balance END AS bal_after
  FROM q JOIN CurrentAccount a ON a.account_id = q.account_id
  WHERE q.n = 1
  UNION ALL
  SELECT q.account_id, q.n, q.payout_id, q.amount, s.bal_after,
         CASE WHEN q.amount <= s.bal_after THEN s.bal_after - q.amount ELSE s.bal_after END
  FROM sim s JOIN q ON q.account_id = s.account_id AND q.n = s.n + 1
)
SELECT payout_id, account_id, amount, bal_before AS balance_at_attempt
FROM sim
WHERE amount > bal_before
ORDER BY payout_id

Another way

WITH RECURSIVE sim AS (
  SELECT p.payout_id, p.account_id, p.amount, p.queued_at, a.opening_balance AS bal_before,
         IF(p.amount <= a.opening_balance, a.opening_balance - p.amount, a.opening_balance) AS bal_after
  FROM QueuedPayout p JOIN CurrentAccount a ON a.account_id = p.account_id
  WHERE NOT EXISTS (SELECT 1 FROM QueuedPayout e WHERE e.account_id = p.account_id
                    AND (e.queued_at < p.queued_at OR (e.queued_at = p.queued_at AND e.payout_id < p.payout_id)))
  UNION ALL
  SELECT nx.payout_id, nx.account_id, nx.amount, nx.queued_at, s.bal_after,
         IF(nx.amount <= s.bal_after, s.bal_after - nx.amount, s.bal_after)
  FROM sim s
  JOIN QueuedPayout nx ON nx.account_id = s.account_id
   AND (nx.queued_at > s.queued_at OR (nx.queued_at = s.queued_at AND nx.payout_id > s.payout_id))
   AND NOT EXISTS (SELECT 1 FROM QueuedPayout m WHERE m.account_id = s.account_id
                   AND (m.queued_at > s.queued_at OR (m.queued_at = s.queued_at AND m.payout_id > s.payout_id))
                   AND (m.queued_at < nx.queued_at OR (m.queued_at = nx.queued_at AND m.payout_id < nx.payout_id)))
)
SELECT payout_id, account_id, amount, bal_before AS balance_at_attempt FROM sim WHERE amount > bal_before ORDER BY payout_id

← Razorpay: Orders Rescued by Switching Payment Method · Razorpay: Merchants Whose Weekly Success Rate Fell 10+ Points →