Game Play Analysis IV

Game Play Analysis IV - Problem

Database Medium

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

Asked in

F Facebook 12 A Amazon 8 G Google 6

Use a CTE to identify first login dates, then self-join the Activity table to find players who returned the next consecutive day. Calculate the fraction by dividing retained players by total unique players.

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

Common Approaches

✓ CTE with Self-Join

⏱️ Time: O(n log n) Space: O(n)

Use a CTE to identify each player's first login date, then self-join the Activity table to count players who logged in on consecutive days. Calculate the fraction by dividing retained players by total players.

CTE with Self-Join — Algorithm Steps

Step 1: Create CTE to find each player's first login date
Step 2: Self-join Activity table to find players who returned the next day
Step 3: Calculate fraction of retained players and round to 2 decimals

Visualization

Tap to expand

Step-by-Step Walkthrough

Find First Login

Group by player, get MIN date

Check Next Day

Join to find consecutive logins

Calculate Fraction

Divide retained by total players

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Due to grouping and joining operations

⚡ Linearithmic

Space Complexity

O(n)

Storage for CTE results and join operations

⚡ Linearithmic Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

CTE with Self-Join — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler