Apple: Running iCloud+ Subscriber Count by Day — SQL Problem
The iCloud+ dashboard shows how the paying base moves day by day.
- Difficulty: Medium
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #614
Problem statement
The iCloud+ dashboard shows how the paying base moves day by day. Each subscribe event adds one subscriber and each cancel event removes one; the count starts at zero before the first event.
For every date that has at least one event, return event_date, net_change (subscribes minus cancels that day) and subscribers, the running total at the end of that day. A day whose events cancel out still appears with net_change 0. Order the rows by event_date.
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: ICloudPlusEvent
| Column | Type |
|---|---|
| event_id | int |
| account_id | int |
| event_date | date |
| event_type | enum(subscribe, cancel) |
Primary key: event_id.
One row per change to an account's iCloud+ status; an account cancels only after subscribing.
Examples
Example 1
ICloudPlusEvent
| event_id | account_id | event_date | event_type |
|---|---|---|---|
| 1 | 11 | 2025-01-01 | subscribe |
| 2 | 12 | 2025-01-01 | subscribe |
| 3 | 13 | 2025-01-02 | subscribe |
| 4 | 11 | 2025-01-02 | cancel |
| 5 | 14 | 2025-01-04 | subscribe |
| 6 | 12 | 2025-01-05 | cancel |
| 7 | 13 | 2025-01-05 | cancel |
Output
| event_date | net_change | subscribers |
|---|---|---|
| 2025-01-01 | 2 | 2 |
| 2025-01-02 | 0 | 2 |
| 2025-01-04 | 1 | 3 |
| 2025-01-05 | -2 | 1 |
How to solve Apple: Running iCloud+ Subscriber Count by Day
Give each event a sign — +1 for subscribe, −1 for cancel — and the day's net change is the sum of those signs, grouped by event_date. A day with one subscribe and one cancel nets to 0 but still has events, so it stays in the output.
The subscriber count at the end of a day is the sum of every net change up to and including that day: a running total. Over the grouped daily rows, SUM(net_change) OVER (ORDER BY event_date) computes exactly that; because the dates are unique after grouping, the default frame (all rows up to the current one) is unambiguous. Running the window over the raw events instead would produce one row per event, and duplicate dates would share a total.
The alternative recomputes each day's total with a correlated subquery over all earlier events — correct but quadratic, where the window is a single sorted pass. Days with no events are not invented; the statement lists only dates that have events.
Reference solution (MySQL)
SELECT event_date, net_change,
SUM(net_change) OVER (ORDER BY event_date) AS subscribers
FROM (
SELECT event_date, SUM(CASE WHEN event_type = 'subscribe' THEN 1 ELSE -1 END) AS net_change
FROM ICloudPlusEvent
GROUP BY event_date
) d
ORDER BY event_dateAnother way
SELECT d.event_date, d.net_change,
(SELECT SUM(IF(e.event_type = 'subscribe', 1, -1)) FROM ICloudPlusEvent e WHERE e.event_date <= d.event_date) AS subscribers
FROM (SELECT event_date, SUM(event_type = 'subscribe') - SUM(event_type = 'cancel') AS net_change FROM ICloudPlusEvent GROUP BY event_date) d
ORDER BY d.event_date← Apple: AppleCare Serial Numbers That Fail the Format Check · Apple: New and Cumulative Unique Apple Arcade Players by Day →