Game Play Analysis IV - Problem

Given a table Activity that tracks player login activity and game sessions, write a SQL query to find the fraction of players that logged in again on the day after their first login.

The table has the following structure:

  • player_id: Unique identifier for each player
  • device_id: Device used for the session
  • event_date: Date of the login session
  • games_played: Number of games played in that session

Your query should return the percentage of players who logged in on consecutive days starting from their first login, rounded to 2 decimal places.

Table Schema

Activity
Column Name Type Description
player_id PK int Unique identifier for each player
device_id int Device used for the gaming session
event_date PK date Date when the player logged in
games_played int Number of games played in that session
Primary Key: (player_id, event_date)
Note: Each row represents a unique player login session on a specific date

Input & Output

Example 1 — Mixed Retention Pattern
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 2018-07-03 5
Output:
fraction
0.33
💡 Note:

Player 1's first login was 2016-03-01 and returned on 2016-03-02 (next day) ✓. Player 2's first login was 2017-06-25 but has no record for 2017-06-26 ✗. Player 3's first login was 2016-03-01 but next login was in 2018 ✗. Result: 1 out of 3 players = 0.33
Example 2 — No Retention
Input Table:
player_id device_id event_date games_played
1 2 2016-03-01 0
1 2 2016-03-04 1
2 3 2017-06-25 2
Output:
fraction
0
💡 Note:

Player 1's first login was 2016-03-01 but next login was 2016-03-04 (not consecutive) ✗. Player 2 only has one login record ✗. No players returned the day after their first login: 0 out of 2 players = 0.00

Constraints

  • 1 ≤ player_id ≤ 1000
  • 1 ≤ device_id ≤ 1000
  • event_date is in format 'YYYY-MM-DD'
  • games_played ≥ 0

Visualization

Tap to expand
Game Play Analysis IV - Player RetentionActivity Tableplayerdevicedate1203-011203-022306-253103-01AnalysisRetention AnalysisPlayer 1: First 03-01✓ Next dayPlayer 2: First 06-25✗ No 06-26Player 3: First 03-01✗ No 03-02Retention Rate1/3 = 0.33Only Player 1 returned the day after first loginFormula: Retained Players ÷ Total Unique Players✓
Understanding the Visualization
1
Input Analysis
Player login activity data
2
First Login Detection
Find minimum date per player
3
Retention Check
Count next-day returns
4
Fraction Calculation
Retained ÷ Total players
Key Takeaway
🎯 Key Insight: Use MIN() to find first login, then self-join to detect consecutive day activity

Related Problems

Asked in
Facebook 12 Amazon 8 Google 6
28.5K Views
Medium 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