Razorpay: Subscription Billing Cycles With No Charge Attempt

Hard
922.

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

ColumnType
subscription_idint
merchant_idint
start_ondate
total_countint
cancelled_ondate

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

ColumnType
attempt_idint
subscription_idint
cycle_noint
attempted_ondate

Primary key: attempt_id.

One row per charge attempt the scheduler made, whatever its result; a cycle may have several.

Example

The rows must come back in the order shown.

Subscription

subscription_idmerchant_idstart_ontotal_countcancelled_on
19012025-03-056NULL
29012025-01-203NULL
39022025-02-10122025-04-15
49032025-06-302NULL

ChargeAttempt

attempt_idsubscription_idcycle_noattempted_on
1112025-03-05
2122025-04-05
3142025-06-05
4212025-01-20
5222025-02-20
6222025-02-21
7232025-03-20
8312025-02-10

Output

subscription_idcycle_nodue_on
132025-05-05
322025-03-10
332025-04-10
412025-06-30
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: 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

ColumnType
subscription_idint
merchant_idint
start_ondate
total_countint
cancelled_ondate

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

ColumnType
attempt_idint
subscription_idint
cycle_noint
attempted_ondate

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_idmerchant_idstart_ontotal_countcancelled_on
19012025-03-056NULL
29012025-01-203NULL
39022025-02-10122025-04-15
49032025-06-302NULL

ChargeAttempt

attempt_idsubscription_idcycle_noattempted_on
1112025-03-05
2122025-04-05
3142025-06-05
4212025-01-20
5222025-02-20
6222025-02-21
7232025-03-20
8312025-02-10

Output

subscription_idcycle_nodue_on
132025-05-05
322025-03-10
332025-04-10
412025-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_no

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