Razorpay: Subscription Cycles Recovered by Retry vs Halted

Medium
909.

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

ColumnTypeValues
charge_idint
subscription_idint
cycle_noint
attempt_noint
attempted_ondate
statusenumsuccess, 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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

SubscriptionCharge

charge_idsubscription_idcycle_noattempt_noattempted_onstatus
170112025-01-05success
270212025-02-05failed
370222025-02-06success
470312025-03-05failed
570322025-03-06failed
670332025-03-08failed
771112025-02-10success
871212025-03-10success
972112025-03-01failed
1072122025-03-02failed
1 more row

Output

subscription_idpaid_first_timerecoveredhalted
70111
71200
72010
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 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

ColumnType
charge_idint
subscription_idint
cycle_noint
attempt_noint
attempted_ondate
statusenum(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_idsubscription_idcycle_noattempt_noattempted_onstatus
170112025-01-05success
270212025-02-05failed
370222025-02-06success
470312025-03-05failed
570322025-03-06failed
670332025-03-08failed
771112025-02-10success
871212025-03-10success
972112025-03-01failed
1072122025-03-02failed
1172132025-03-04success

Output

subscription_idpaid_first_timerecoveredhalted
70111
71200
72010

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_id

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