Game Play Analysis V - Problem
You are given a table Activity that records player gaming sessions.
The install date of a player is defined as their first login day.
The day one retention for install date X is calculated as:
- Number of players who installed on date X AND logged back in the next day
- Divided by total number of players who installed on date X
- Rounded to 2 decimal places
Write a SQL query to report for each install date:
- The number of players that installed the game on that day
- The day one retention rate
Table Schema
Activity
| Column Name | Type | Description |
|---|---|---|
player_id
PK
|
int | Unique identifier for each player |
device_id
|
int | Device used for gaming session |
event_date
PK
|
date | Date of the gaming session |
games_played
|
int | Number of games played in this session |
Primary Key: (player_id, event_date)
Note: Each row represents a player's gaming session on a specific date
Input & Output
Example 1 — Mixed Retention Rates
Input Table:
| 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-03-02 | 5 |
Output:
| install_date | installs | day1_retention |
|---|---|---|
| 2016-03-01 | 2 | 0.5 |
| 2017-06-25 | 1 | 0 |
💡 Note:
Player 1 and 3 both installed on 2016-03-01. Both returned on 2016-03-02, giving 100% retention. Player 2 installed on 2017-06-25 but didn't return the next day, giving 0% retention.
Example 2 — No Next-Day Returns
Input Table:
| player_id | device_id | event_date | games_played |
|---|---|---|---|
| 1 | 1 | 2016-03-01 | 3 |
| 1 | 1 | 2016-03-03 | 2 |
| 2 | 2 | 2016-03-01 | 1 |
Output:
| install_date | installs | day1_retention |
|---|---|---|
| 2016-03-01 | 2 | 0 |
💡 Note:
Both players installed on 2016-03-01. Player 1 returned on 2016-03-03 (not the next day) and Player 2 never returned, resulting in 0% day-one retention.
Constraints
-
1 ≤ player_id ≤ 10000 -
1 ≤ device_id ≤ 100 -
event_dateis a valid date -
0 ≤ games_played ≤ 1000
Visualization
Tap to expand
Understanding the Visualization
1
Identify Installs
Find first login date per player
2
Check Returns
Match next-day logins
3
Calculate Retention
Group by install date and compute rates
Key Takeaway
🎯 Key Insight: Use MIN() to find install dates, then self-join with date arithmetic to measure day-one retention
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code