Install Cohort Day-1 Retention Report

Hard

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: install_dt installs (number of players whose first login is install_dt) 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 1

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 2

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 3

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.

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

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 |

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 |

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 |