Apple: Running iCloud+ Subscriber Count by Day

Medium
614.

Apple: Running iCloud+ Subscriber Count by Day

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

ColumnTypeValues
event_idint
account_idint
event_datedate
event_typeenumsubscribe, cancel

Primary key: event_id.

One row per change to an account's iCloud+ status; an account cancels only after subscribing.

Example

The rows must come back in the order shown.

ICloudPlusEvent

event_idaccount_idevent_dateevent_type
1112025-01-01subscribe
2122025-01-01subscribe
3132025-01-02subscribe
4112025-01-02cancel
5142025-01-04subscribe
6122025-01-05cancel
7132025-01-05cancel

Output

event_datenet_changesubscribers
2025-01-0122
2025-01-0202
2025-01-0413
2025-01-05-21
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: 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

ColumnType
event_idint
account_idint
event_datedate
event_typeenum(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_idaccount_idevent_dateevent_type
1112025-01-01subscribe
2122025-01-01subscribe
3132025-01-02subscribe
4112025-01-02cancel
5142025-01-04subscribe
6122025-01-05cancel
7132025-01-05cancel

Output

event_datenet_changesubscribers
2025-01-0122
2025-01-0202
2025-01-0413
2025-01-05-21

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_date

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