Player Day-After Retention Rate

Medium

You are given a gameplay table named Activity. Each row captures one player's activity on a calendar date: player_id: player identifier device_id: device used for that session day event_date: session date (YYYY-MM-DD) games_played: number of games played that day The pair (player_id, event_date) is unique. Define a retained player as a player who has at least one record on the day immediately after their first-ever login date. Compute the retention fraction: retained_players / total_distinct_players Return a single-row result with one column: fraction The value must be rounded to 2 decimal places. This problem supports both: 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-02  | 0            |
| 3         | 4         | 2018-07-03  | 5            |

Output

[
  {"fraction": 0.33}
]

Explanation

Only player 1 returns exactly one day after first login. So fraction = 1/3 = 0.33.

Example 2

Input

Activity:
| player_id | device_id | event_date  | games_played |
|-----------|-----------|-------------|--------------|
| 10        | 5         | 2022-01-01  | 4            |
| 10        | 2         | 2022-01-02  | 1            |
| 11        | 7         | 2022-02-10  | 0            |
| 11        | 9         | 2022-02-11  | 8            |

Output

[
  {"fraction": 1.0}
]

Explanation

Both players return on the day after their first login. Fraction = 2/2 = 1.00.

Example 3

Input

Activity:
| player_id | device_id | event_date  | games_played |
|-----------|-----------|-------------|--------------|
| 7         | 1         | 2023-03-01  | 2            |
| 7         | 3         | 2023-03-04  | 5            |
| 8         | 2         | 2023-08-15  | 0            |

Output

[
  {"fraction": 0.0}
]

Explanation

No player has activity on first_login + 1 day. Fraction = 0/2 = 0.00.

Constraints

1 <= number of rows in Activity <= 200,000
1 <= player_id <= 1,000,000,000
1 <= device_id <= 1,000,000,000
0 <= games_played <= 100,000
event_date is a valid date in YYYY-MM-DD format
(player_id, event_date) is unique
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with one column: fraction

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-02 | 0 | | 3 | 4 | 2018-07-03 | 5 |

Activity: | player_id | device_id | event_date | games_played | |-----------|-----------|-------------|--------------| | 10 | 5 | 2022-01-01 | 4 | | 10 | 2 | 2022-01-02 | 1 | | 11 | 7 | 2022-02-10 | 0 | | 11 | 9 | 2022-02-11 | 8 |

Activity: | player_id | device_id | event_date | games_played | |-----------|-----------|-------------|--------------| | 7 | 1 | 2023-03-01 | 2 | | 7 | 3 | 2023-03-04 | 5 | | 8 | 2 | 2023-08-15 | 0 |