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
| Column | Type |
|---|---|
| subscription_id | int |
| app_name | varchar |
| started_on | date |
Primary key: subscription_id.
One row per auto-renewing subscription; an app has many subscribers.
Table: BillingAttempt
| Column | Type |
|---|---|
| attempt_id | int |
| subscription_id | int |
| attempted_on | date |
| outcome | enum(success, failure) |
Primary key: attempt_id.
One row per charge attempt, renewal or retry.
Examples
Example 1
AppSubscription
| subscription_id | app_name | started_on |
|---|---|---|
| 1 | Yoga Streak | 2024-01-10 |
| 2 | Yoga Streak | 2024-02-01 |
| 3 | Raga Tuner | 2024-03-15 |
BillingAttempt
| attempt_id | subscription_id | attempted_on | outcome |
|---|---|---|---|
| 1 | 1 | 2025-01-10 | success |
| 2 | 1 | 2025-02-10 | failure |
| 3 | 1 | 2025-02-15 | failure |
| 4 | 1 | 2025-02-26 | success |
| 5 | 1 | 2025-03-26 | failure |
| 6 | 1 | 2025-04-20 | success |
| 7 | 2 | 2025-02-01 | failure |
| 8 | 2 | 2025-02-09 | failure |
| 9 | 3 | 2025-03-15 | success |
| 10 | 3 | 2025-04-15 | success |
Output
| app_name | lapses | recovered_in_grace |
|---|---|---|
| Yoga Streak | 3 | 1 |
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_nameAnother 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 →