Razorpay: Subscription Billing Cycles With No Charge Attempt
An audit of Razorpay Subscriptions checks that the scheduler attempted a charge for every billing cycle that fell due.
- Difficulty: Hard
- Topics: Subqueries, Dates, Joins
- Dialect: MySQL
- Problem: #922
Problem statement
An audit of Razorpay Subscriptions checks that the scheduler attempted a charge for every billing cycle that fell due. A cycle with no attempt at all is lost revenue for the merchant.
Subscriptions bill monthly: cycle k (1 ≤ k ≤ total_count) falls due on start_on plus k − 1 months. A cycle is expected when its due date is on or before 2025-06-30 and, for a cancelled subscription, on or before cancelled_on. Return every expected cycle with no row in ChargeAttempt: subscription_id, cycle_no and due_on, ordered by subscription_id, then cycle_no.
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: Subscription
| Column | Type |
|---|---|
| subscription_id | int |
| merchant_id | int |
| start_on | date |
| total_count | int |
| cancelled_on | date |
Primary key: subscription_id.
total_count is the number of monthly cycles the customer authorised (1–12); start_on is always on day 1–28 of a month; cancelled_on is NULL unless the subscription was cancelled.
Table: ChargeAttempt
| Column | Type |
|---|---|
| attempt_id | int |
| subscription_id | int |
| cycle_no | int |
| attempted_on | date |
Primary key: attempt_id.
One row per charge attempt the scheduler made, whatever its result; a cycle may have several.
Examples
Example 1
Subscription
| subscription_id | merchant_id | start_on | total_count | cancelled_on |
|---|---|---|---|---|
| 1 | 901 | 2025-03-05 | 6 | NULL |
| 2 | 901 | 2025-01-20 | 3 | NULL |
| 3 | 902 | 2025-02-10 | 12 | 2025-04-15 |
| 4 | 903 | 2025-06-30 | 2 | NULL |
ChargeAttempt
| attempt_id | subscription_id | cycle_no | attempted_on |
|---|---|---|---|
| 1 | 1 | 1 | 2025-03-05 |
| 2 | 1 | 2 | 2025-04-05 |
| 3 | 1 | 4 | 2025-06-05 |
| 4 | 2 | 1 | 2025-01-20 |
| 5 | 2 | 2 | 2025-02-20 |
| 6 | 2 | 2 | 2025-02-21 |
| 7 | 2 | 3 | 2025-03-20 |
| 8 | 3 | 1 | 2025-02-10 |
Output
| subscription_id | cycle_no | due_on |
|---|---|---|
| 1 | 3 | 2025-05-05 |
| 3 | 2 | 2025-03-10 |
| 3 | 3 | 2025-04-10 |
| 4 | 1 | 2025-06-30 |
How to solve Razorpay: Subscription Billing Cycles With No Charge Attempt
The missing cycles cannot be found by looking at ChargeAttempt alone — a cycle with no attempt leaves no trace there. So first generate the cycles that should exist: one row per subscription and cycle number 1 … total_count. A recursive CTE does this: the anchor is cycle 1 due on start_on, and each step adds one to the cycle number while it is below total_count, computing the due date as start_on plus cycle_no months (the previous cycle number is the new one minus one). Deriving every due date from the start date, never from the previous due date, avoids drift; the start day is at most 28, so every month has that day.
Then filter to expected cycles — due by the report date and, for a cancelled subscription, by its cancellation date (a NULL cancelled_on passes through the IS NULL branch) — and keep those with no attempt using NOT EXISTS on (subscription_id, cycle_no). Several attempts on one cycle do not matter: existence is all that is checked.
The alternative replaces the recursion with an inline numbers table 1–12 joined on n <= total_count, and the anti join with LEFT JOIN … IS NULL. It is simpler but needs the cap on total_count; the recursive version works for any length. Both are linear in the number of cycles generated.
Reference solution (MySQL)
WITH RECURSIVE cyc AS (
SELECT subscription_id, start_on, total_count, cancelled_on, 1 AS cycle_no, start_on AS due_on
FROM Subscription
UNION ALL
SELECT subscription_id, start_on, total_count, cancelled_on, cycle_no + 1, DATE_ADD(start_on, INTERVAL cycle_no MONTH)
FROM cyc
WHERE cycle_no < total_count
)
SELECT c.subscription_id, c.cycle_no, c.due_on
FROM cyc c
WHERE c.due_on <= '2025-06-30'
AND (c.cancelled_on IS NULL OR c.due_on <= c.cancelled_on)
AND NOT EXISTS (SELECT 1 FROM ChargeAttempt a WHERE a.subscription_id = c.subscription_id AND a.cycle_no = c.cycle_no)
ORDER BY c.subscription_id, c.cycle_noAnother way
SELECT s.subscription_id, k.n AS cycle_no, DATE_ADD(s.start_on, INTERVAL k.n - 1 MONTH) AS due_on
FROM Subscription s
JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) k
ON k.n <= s.total_count
LEFT JOIN ChargeAttempt a ON a.subscription_id = s.subscription_id AND a.cycle_no = k.n
WHERE a.attempt_id IS NULL
AND DATE_ADD(s.start_on, INTERVAL k.n - 1 MONTH) <= '2025-06-30'
AND (s.cancelled_on IS NULL OR DATE_ADD(s.start_on, INTERVAL k.n - 1 MONTH) <= s.cancelled_on)
ORDER BY s.subscription_id, k.n← Razorpay: Merchants Whose Weekly Success Rate Fell 10+ Points · Spotify: Explicit Tracks Sitting on Kids Playlists →