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
| Column | Type |
|---|---|
| account_id | int |
| business_name | varchar |
| opening_balance | int |
Primary key: account_id.
A RazorpayX current account and its balance before the queue is processed.
Table: QueuedPayout
| Column | Type |
|---|---|
| payout_id | int |
| account_id | int |
| amount | int |
| queued_at | datetime |
Primary key: payout_id.
Payouts waiting for funds; account_id always names an account.
Examples
Example 1
CurrentAccount
| account_id | business_name | opening_balance |
|---|---|---|
| 1 | Kirana Kart | 50000 |
| 2 | PixelPlay Studio | 20000 |
| 3 | BookNook | 10000 |
QueuedPayout
| payout_id | account_id | amount | queued_at |
|---|---|---|---|
| 101 | 1 | 30000 | 2025-03-10 09:00:00 |
| 102 | 1 | 25000 | 2025-03-10 09:05:00 |
| 103 | 1 | 15000 | 2025-03-10 09:10:00 |
| 104 | 1 | 5000 | 2025-03-10 09:20:00 |
| 105 | 1 | 1000 | 2025-03-10 09:30:00 |
| 106 | 2 | 20000 | 2025-03-10 10:00:00 |
| 107 | 2 | 1 | 2025-03-10 10:00:00 |
| 108 | 3 | 4000 | 2025-03-10 11:00:00 |
Output
| payout_id | account_id | amount | balance_at_attempt |
|---|---|---|---|
| 102 | 1 | 25000 | 20000 |
| 105 | 1 | 1000 | 0 |
| 107 | 2 | 1 | 0 |
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_idAnother 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 →