Apple: Subscription Billing Lapses Recovered in the Grace Period

Hard
617.

Apple: Subscription Billing Lapses Recovered in the Grace Period

When an App Store subscription renewal fails to charge, the store keeps retrying, and the subscriber keeps access during a 16-day grace period. The commerce team measures how many lapses are recovered within it.

Within one subscription, order its billing attempts by attempted_on, then attempt_id. A lapse begins at a failure that is the subscription's first attempt or that directly follows a success; later failures before the next success belong to the same lapse. A lapse is recovered in grace if the subscription's first success after the lapse's first failure comes at most 16 days after that failure. For each app_name with at least one lapse, return app_name, lapses and recovered_in_grace. Return the rows in any order.

A practice scenario set at Apple: the tables and data are invented, and this is not a question from, or endorsed by, Apple.

Tables

Table: AppSubscription

ColumnType
subscription_idint
app_namevarchar
started_ondate

Primary key: subscription_id.

One row per auto-renewing subscription; an app has many subscribers.

Table: BillingAttempt

ColumnTypeValues
attempt_idint
subscription_idint
attempted_ondate
outcomeenumsuccess, failure

Primary key: attempt_id.

One row per charge attempt, renewal or retry.

Example

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

AppSubscription

subscription_idapp_namestarted_on
1Yoga Streak2024-01-10
2Yoga Streak2024-02-01
3Raga Tuner2024-03-15

BillingAttempt

attempt_idsubscription_idattempted_onoutcome
112025-01-10success
212025-02-10failure
312025-02-15failure
412025-02-26success
512025-03-26failure
612025-04-20success
722025-02-01failure
822025-02-09failure
932025-03-15success
1032025-04-15success

Output

app_namelapsesrecovered_in_grace
Yoga Streak31
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.

Apple: Subscription Billing Lapses Recovered in the Grace Period

When an App Store subscription renewal fails to charge, the store keeps retrying, and the subscriber keeps access during a 16-day grace period.

  • Difficulty: Hard
  • Topics: Window Functions, Subqueries, Dates
  • Dialect: MySQL
  • Problem: #617

Problem statement

When an App Store subscription renewal fails to charge, the store keeps retrying, and the subscriber keeps access during a 16-day grace period. The commerce team measures how many lapses are recovered within it.

Within one subscription, order its billing attempts by attempted_on, then attempt_id. A lapse begins at a failure that is the subscription's first attempt or that directly follows a success; later failures before the next success belong to the same lapse. A lapse is recovered in grace if the subscription's first success after the lapse's first failure comes at most 16 days after that failure. For each app_name with at least one lapse, return app_name, lapses and recovered_in_grace. Return the rows in any order.

A practice scenario set at Apple: the tables and data are invented, and this is not a question from, or endorsed by, Apple.

Tables

Table: AppSubscription

ColumnType
subscription_idint
app_namevarchar
started_ondate

Primary key: subscription_id.

One row per auto-renewing subscription; an app has many subscribers.

Table: BillingAttempt

ColumnType
attempt_idint
subscription_idint
attempted_ondate
outcomeenum(success, failure)

Primary key: attempt_id.

One row per charge attempt, renewal or retry.

Examples

Example 1

AppSubscription

subscription_idapp_namestarted_on
1Yoga Streak2024-01-10
2Yoga Streak2024-02-01
3Raga Tuner2024-03-15

BillingAttempt

attempt_idsubscription_idattempted_onoutcome
112025-01-10success
212025-02-10failure
312025-02-15failure
412025-02-26success
512025-03-26failure
612025-04-20success
722025-02-01failure
822025-02-09failure
932025-03-15success
1032025-04-15success

Output

app_namelapsesrecovered_in_grace
Yoga Streak31

How to solve Apple: Subscription Billing Lapses Recovered in the Grace Period

Two ideas are layered here. First, a lapse is an island of consecutive failures, and only its first failure matters. LAG(outcome) over each subscription's ordered attempts tells whether a failure starts an island: it does when the previous outcome is a success or there is no previous attempt. Retries that fail again are inside the same lapse and must not be counted twice — subscription 1's two February failures are one lapse.

Second, recovery looks forward: the first success after the lapse's first failure. A correlated MIN over later successes finds it; a lapse with no later success gets NULL and is counted as a lapse but not a recovery (subscription 2). The grace test is DATEDIFF(recovered_on, failed_on) <= 16 — 16 days in counts, 17 does not.

The alternative does the look-ahead with a window instead: MIN(CASE WHEN outcome = 'success' THEN attempted_on END) over the frame ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING is the next success date for every row, so no subquery is needed. Finally the lapses join their subscription and are counted per app with a conditional SUM. The window version is a single sorted pass per subscription.

Reference solution (MySQL)

WITH ordered AS (
  SELECT attempt_id, subscription_id, attempted_on, outcome,
         LAG(outcome) OVER (PARTITION BY subscription_id ORDER BY attempted_on, attempt_id) AS prev_outcome
  FROM BillingAttempt
), lapse AS (
  SELECT o.subscription_id, o.attempted_on,
         (SELECT MIN(s.attempted_on) FROM BillingAttempt s
          WHERE s.subscription_id = o.subscription_id AND s.outcome = 'success'
            AND (s.attempted_on > o.attempted_on OR (s.attempted_on = o.attempted_on AND s.attempt_id > o.attempt_id))) AS recovered_on
  FROM ordered o
  WHERE o.outcome = 'failure' AND (o.prev_outcome IS NULL OR o.prev_outcome = 'success')
)
SELECT a.app_name, COUNT(*) AS lapses,
       SUM(CASE WHEN l.recovered_on IS NOT NULL AND DATEDIFF(l.recovered_on, l.attempted_on) <= 16 THEN 1 ELSE 0 END) AS recovered_in_grace
FROM lapse l
JOIN AppSubscription a ON a.subscription_id = l.subscription_id
GROUP BY a.app_name

Another way

WITH marked AS (
  SELECT subscription_id, attempted_on, outcome,
         LAG(outcome) OVER w AS prev_outcome,
         MIN(CASE WHEN outcome = 'success' THEN attempted_on END)
           OVER (PARTITION BY subscription_id ORDER BY attempted_on, attempt_id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS next_success
  FROM BillingAttempt
  WINDOW w AS (PARTITION BY subscription_id ORDER BY attempted_on, attempt_id)
)
SELECT a.app_name, COUNT(*) AS lapses,
       SUM(IF(DATEDIFF(m.next_success, m.attempted_on) <= 16, 1, 0)) AS recovered_in_grace
FROM marked m
JOIN AppSubscription a ON a.subscription_id = m.subscription_id
WHERE m.outcome = 'failure' AND COALESCE(m.prev_outcome, 'success') = 'success'
GROUP BY a.app_name

← Apple: Leaf Parts Needed for Each Mac Build Order · Apple: In-App Revenue at the Price in Effect on Each Day →