Apple: New and Cumulative Unique Apple Arcade Players by Day
After a new Apple Arcade title launches, the Arcade growth team tracks how fast the player base grows: not sessions, but distinct people — and how many of…
- Difficulty: Medium
- Topics: Window Functions, Aggregation, Dates
- Dialect: MySQL
- Problem: #615
Problem statement
After a new Apple Arcade title launches, the Arcade growth team tracks how fast the player base grows: not sessions, but distinct people — and how many of each day's players are new.
For every played_on date that has at least one session, return played_on, players (distinct players who played that day), new_players (players whose first session ever in the table is on that day) and total_players (distinct players who have played on or before that day). A player who comes back on a later day is counted in that day's players but never again in new_players or total_players. Order the rows by played_on.
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: ArcadeSession
| Column | Type |
|---|---|
| session_id | int |
| player_id | int |
| played_on | date |
| game | varchar |
Primary key: session_id.
One row per play session; a player can have several sessions on one day, in one or several games.
Examples
Example 1
ArcadeSession
| session_id | player_id | played_on | game |
|---|---|---|---|
| 1 | 101 | 2025-04-01 | Monsoon Racer |
| 2 | 101 | 2025-04-01 | Rangoli Puzzle |
| 3 | 102 | 2025-04-01 | Monsoon Racer |
| 4 | 101 | 2025-04-02 | Monsoon Racer |
| 5 | 103 | 2025-04-02 | Tabla Hero |
| 6 | 102 | 2025-04-04 | Tabla Hero |
| 7 | 101 | 2025-04-04 | Rangoli Puzzle |
| 8 | 104 | 2025-04-05 | Monsoon Racer |
| 9 | 104 | 2025-04-05 | Tabla Hero |
Output
| played_on | players | new_players | total_players |
|---|---|---|---|
| 2025-04-01 | 2 | 2 | 2 |
| 2025-04-02 | 2 | 1 | 3 |
| 2025-04-04 | 2 | 0 | 3 |
| 2025-04-05 | 1 | 1 | 4 |
How to solve Apple: New and Cumulative Unique Apple Arcade Players by Day
Two of the counts are easy: players is COUNT(DISTINCT player_id) per day. The cumulative one is the trap. Adding up the daily distinct counts counts every returning player again, and MySQL has no COUNT(DISTINCT …) OVER (ORDER BY …). The way out is to give every player one date on which they count: their first session, MIN(played_on) per player.
Grouping those first days by date gives new_players per day, and since each player has exactly one first day, a running SUM(new_players) OVER (ORDER BY played_on) is the number of distinct players seen so far. LEFT JOIN the arrivals onto the days that had sessions, so a day on which only returning players played (2025-04-04 in the example) keeps its row with 0 new players and an unchanged total.
The alternative computes the total with a correlated COUNT(DISTINCT) over all sessions up to each day, and the new players with NOT EXISTS (no earlier session of the same player). It is correct but rescans the history for every day; the first-seen version reads the sessions twice and sorts the days once.
Reference solution (MySQL)
WITH first_seen AS (
SELECT player_id, MIN(played_on) AS first_day
FROM ArcadeSession
GROUP BY player_id
), arrivals AS (
SELECT first_day, COUNT(*) AS new_players
FROM first_seen
GROUP BY first_day
), daily AS (
SELECT played_on, COUNT(DISTINCT player_id) AS players
FROM ArcadeSession
GROUP BY played_on
)
SELECT d.played_on, d.players,
COALESCE(a.new_players, 0) AS new_players,
SUM(COALESCE(a.new_players, 0)) OVER (ORDER BY d.played_on) AS total_players
FROM daily d
LEFT JOIN arrivals a ON a.first_day = d.played_on
ORDER BY d.played_onAnother way
SELECT d.played_on,
COUNT(DISTINCT s.player_id) AS players,
COUNT(DISTINCT CASE WHEN NOT EXISTS (SELECT 1 FROM ArcadeSession e WHERE e.player_id = s.player_id AND e.played_on < s.played_on) THEN s.player_id END) AS new_players,
(SELECT COUNT(DISTINCT t.player_id) FROM ArcadeSession t WHERE t.played_on <= d.played_on) AS total_players
FROM (SELECT DISTINCT played_on FROM ArcadeSession) d
JOIN ArcadeSession s ON s.played_on = d.played_on
GROUP BY d.played_on
ORDER BY d.played_on← Apple: Running iCloud+ Subscriber Count by Day · Apple: Leaf Parts Needed for Each Mac Build Order →