Apple: New and Cumulative Unique Apple Arcade Players by Day

Medium
615.

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

ColumnType
session_idint
player_idint
played_ondate
gamevarchar

Primary key: session_id.

One row per play session; a player can have several sessions on one day, in one or several games.

Example

The rows must come back in the order shown.

ArcadeSession

session_idplayer_idplayed_ongame
11012025-04-01Monsoon Racer
21012025-04-01Rangoli Puzzle
31022025-04-01Monsoon Racer
41012025-04-02Monsoon Racer
51032025-04-02Tabla Hero
61022025-04-04Tabla Hero
71012025-04-04Rangoli Puzzle
81042025-04-05Monsoon Racer
91042025-04-05Tabla Hero

Output

played_onplayersnew_playerstotal_players
2025-04-01222
2025-04-02213
2025-04-04203
2025-04-05114
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: 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

ColumnType
session_idint
player_idint
played_ondate
gamevarchar

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_idplayer_idplayed_ongame
11012025-04-01Monsoon Racer
21012025-04-01Rangoli Puzzle
31022025-04-01Monsoon Racer
41012025-04-02Monsoon Racer
51032025-04-02Tabla Hero
61022025-04-04Tabla Hero
71012025-04-04Rangoli Puzzle
81042025-04-05Monsoon Racer
91042025-04-05Tabla Hero

Output

played_onplayersnew_playerstotal_players
2025-04-01222
2025-04-02213
2025-04-04203
2025-04-05114

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_on

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