Razorpay: Subscription Cycles Recovered by Retry vs Halted
When a subscription's recurring charge fails, Razorpay Subscriptions retries it; if every retry fails, the subscription halts.
- Difficulty: Medium
- Topics: Subqueries, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #909
Problem statement
When a subscription's recurring charge fails, Razorpay Subscriptions retries it; if every retry fails, the subscription halts. The billing team wants, per subscription, how its billing cycles ended up.
Each billing cycle of a subscription is exactly one of: paid first time (attempt 1 succeeded), recovered (a later attempt succeeded), or halted (no attempt succeeded). For every subscription with at least one charge row, return subscription_id, paid_first_time, recovered and halted — the number of its cycles of each kind. Any order.
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: SubscriptionCharge
| Column | Type |
|---|---|
| charge_id | int |
| subscription_id | int |
| cycle_no | int |
| attempt_no | int |
| attempted_on | date |
| status | enum(success, failed) |
Primary key: charge_id.
One row per charge attempt on a billing cycle. Attempts of a cycle are numbered from 1; retrying stops at the first success, so a cycle has at most one success and it is its last attempt.
Examples
Example 1
SubscriptionCharge
| charge_id | subscription_id | cycle_no | attempt_no | attempted_on | status |
|---|---|---|---|---|---|
| 1 | 70 | 1 | 1 | 2025-01-05 | success |
| 2 | 70 | 2 | 1 | 2025-02-05 | failed |
| 3 | 70 | 2 | 2 | 2025-02-06 | success |
| 4 | 70 | 3 | 1 | 2025-03-05 | failed |
| 5 | 70 | 3 | 2 | 2025-03-06 | failed |
| 6 | 70 | 3 | 3 | 2025-03-08 | failed |
| 7 | 71 | 1 | 1 | 2025-02-10 | success |
| 8 | 71 | 2 | 1 | 2025-03-10 | success |
| 9 | 72 | 1 | 1 | 2025-03-01 | failed |
| 10 | 72 | 1 | 2 | 2025-03-02 | failed |
| 11 | 72 | 1 | 3 | 2025-03-04 | success |
Output
| subscription_id | paid_first_time | recovered | halted |
|---|---|---|---|
| 70 | 1 | 1 | 1 |
| 71 | 2 | 0 | 0 |
| 72 | 0 | 1 | 0 |
How to solve Razorpay: Subscription Cycles Recovered by Retry vs Halted
The outcome belongs to a cycle, not to a row, so the query has two levels. The inner query groups by (subscription_id, cycle_no) and computes ok_at, the attempt number of the successful charge: MIN(CASE WHEN status = 'success' THEN attempt_no END) — the CASE yields NULL for failed rows, and MIN ignores NULLs, so ok_at is NULL exactly when the cycle never succeeded.
The outer query groups those cycle rows by subscription and counts the three classes with conditional sums: ok_at = 1 is paid first time, ok_at > 1 recovered, ok_at IS NULL halted. Every cycle lands in exactly one bucket, so the three columns add up to the number of cycles.
The alternative uses the table's guarantee — at most one success per cycle — to do it in one pass: successes on attempt 1 and on later attempts are conditional sums of rows, and halted cycles are the distinct cycles minus the successes. It is shorter but leans on that rule; the two-level version would still be right if a cycle could hold two successes.
Reference solution (MySQL)
SELECT subscription_id,
SUM(CASE WHEN ok_at = 1 THEN 1 ELSE 0 END) AS paid_first_time,
SUM(CASE WHEN ok_at > 1 THEN 1 ELSE 0 END) AS recovered,
SUM(CASE WHEN ok_at IS NULL THEN 1 ELSE 0 END) AS halted
FROM (SELECT subscription_id, cycle_no,
MIN(CASE WHEN status = 'success' THEN attempt_no END) AS ok_at
FROM SubscriptionCharge
GROUP BY subscription_id, cycle_no) c
GROUP BY subscription_idAnother way
SELECT subscription_id,
SUM(status = 'success' AND attempt_no = 1) AS paid_first_time,
SUM(status = 'success' AND attempt_no > 1) AS recovered,
COUNT(DISTINCT cycle_no) - SUM(status = 'success') AS halted
FROM SubscriptionCharge
GROUP BY subscription_id← Razorpay: Webhook Events That Never Got a 2xx Response · Razorpay: Leading Card Network in Each Merchant Category →