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 1
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_date is a valid date
  • 0 ≤ games_played ≤ 1000

Visualization

Tap to expand
Game Play Analysis V INPUT Activity Table player device login_date games 1 2 2016-03-01 5 1 2 2016-03-02 6 2 3 2016-03-01 1 3 1 2016-03-02 0 3 4 2016-03-03 5 Key Definitions Install Date = First login date Day 1 Retention = Players who return on install_date + 1 day Player Timeline Day 0 Install Day 1 Retain? Day 2+ ALGORITHM STEPS 1 Find Install Dates MIN(login_date) per player SELECT player, MIN(date) 2 Count Installs GROUP BY install_date COUNT(player) AS installs 3 Check Day 1 Return LEFT JOIN on date + 1 JOIN Activity ON install + 1 = login 4 Calculate Retention retained / installs ratio ROUND(COUNT(a2.player) / COUNT(a1.player), 2) FINAL RESULT Output Table install_dt installs D1_ret 2016-03-01 2 0.50 2016-03-02 1 1.00 Calculation Breakdown 2016-03-01 Installed: Player 1, Player 2 Returned Day 1: Player 1 only Retention = 1/2 = 0.50 2016-03-02 Installed: Player 3 Returned Day 1: Player 3 Retention = 1/1 = 1.00 OK Key Insight: Use a CTE or subquery to first identify each player's install date (MIN login_date), then perform a self-join with DATE_ADD to check if they logged in the next day. The retention rate is calculated as the ratio of players who returned on day 1 to total installs, grouped by install date. Use ROUND for 2 decimals. TutorialsPoint - Game Play Analysis V | Optimal Solution

Related Problems

Asked in
Facebook 28 Amazon 22 Google 18
32.5K Views
Medium-High Frequency
~18 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen