Install Cohort Day-1 Retention Report

HARD30 pts

A game analytics pipeline stores player activity in one table:

activity columns:

player_id: player identifier
device_id: device used during that daily session
event_date: session date in YYYY-MM-DD
games_played: number of games played that day

The pair (player_id, event_date) is unique.

Definitions:

install_dt for a player = the earliest event_date for that player.
day-1 retained player = a player who has an event on install_dt + 1 day.
day1_retention for cohort date x = retained_players_in_cohort_x / installs_in_cohort_x.

Task:

For each install_dt cohort, return:
1. install_dt
2. installs (number of players whose first login is install_dt)
3. day1_retention rounded to 2 decimals

Output requirements:

Return columns: install_dt, installs, day1_retention
Sort by install_dt ascending

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

activity:
| player_id | device_id | event_date  | games_played |
|-----------|-----------|-------------|--------------|
| 1         | 2         | 2016-03-01  | 5            |
| 1         | 2         | 2016-03-02  | 6            |
| 2         | 3         | 2017-06-25  | 1            |
| 3         | 1         | 2016-03-01  | 0            |
| 3         | 4         | 2016-07-03  | 5            |

Output

[
  {"install_dt":"2016-03-01","installs":2,"day1_retention":0.5},
  {"install_dt":"2017-06-25","installs":1,"day1_retention":0.0}
]

Explanation

For 2016-03-01, players 1 and 3 installed, and only player 1 returned on day+1. For 2017-06-25, player 2 did not return on day+1.

Example

Input

activity:
| player_id | device_id | event_date  | games_played |
|-----------|-----------|-------------|--------------|
| 10        | 9         | 2024-01-10  | 3            |
| 10        | 9         | 2024-01-11  | 1            |
| 11        | 5         | 2024-01-10  | 2            |
| 11        | 8         | 2024-01-11  | 4            |
| 12        | 4         | 2024-01-15  | 7            |

Output

[
  {"install_dt":"2024-01-10","installs":2,"day1_retention":1.0},
  {"install_dt":"2024-01-15","installs":1,"day1_retention":0.0}
]

Explanation

All players in cohort 2024-01-10 returned next day; player 12 did not.

Example

Input

activity:
| player_id | device_id | event_date  | games_played |
|-----------|-----------|-------------|--------------|
| 100       | 3         | 2023-08-01  | 1            |
| 101       | 2         | 2023-08-01  | 5            |
| 102       | 4         | 2023-08-01  | 0            |
| 100       | 5         | 2023-08-05  | 9            |
| 101       | 2         | 2023-08-02  | 2            |

Output

[
  {"install_dt":"2023-08-01","installs":3,"day1_retention":0.33}
]

Explanation

Only player 101 returns on day+1. Cohort retention is 1/3 = 0.33.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in activity <= 200000
1 <= player_id, device_id <= 1000000000
0 <= games_played <= 100000
event_date is a valid YYYY-MM-DD date
(player_id, event_date) is unique
Use exact day+1 logic for retention
Return day1_retention rounded to two decimal places
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: install_dt, installs, day1_retention

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

activity =

[{"player_id":1,"device_id":2,"event_date":"2016-03-01","games_played":5},{"player_id":1,"device_id":2,"event_date":"2016-03-02","games_played":6},{"player_id":2,"device_id":3,"event_date":"2017-06-25","games_played":1},{"player_id":3,"device_id":1,"event_date":"2016-03-01","games_played":0},{"player_id":3,"device_id":4,"event_date":"2016-07-03","games_played":5}]

Install Cohort Day-1 Retention Report

Hints

Install Cohort Day-1 Retention Report

Hints