Game Play Analysis IV - Practice Coding Problems

Game Play Analysis IV - Problem

Database Medium

Imagine you're a data analyst at a gaming company trying to measure player retention! 🎮

You have an Activity table that tracks every time players log in and play games. Your mission is to calculate the fraction of players who came back the very next day after their first login.

Table Structure:

Column Name	Type
player_id	int
device_id	int
event_date	date
games_played	int

The (player_id, event_date) combination is unique, meaning each player can only have one record per day.

Your Goal: Find what percentage of players logged in on consecutive days starting from their very first login. Return this as a decimal rounded to 2 places.

Example: If 100 players joined your game, and 33 of them came back the next day, your answer would be 0.33.

Input & Output

example_1.py — Basic Case

$ Input: Activity 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-07-03 | 5 |

› Output: 0.33

💡 Note: Player 1 first logged in on 2016-03-01 and came back on 2016-03-02 (consecutive). Player 2 first logged in on 2017-06-25 and never returned. Player 3 first logged in on 2016-03-01 but next login was 2016-07-03 (not consecutive). Only 1 out of 3 players logged in consecutively, so 1/3 = 0.33.

example_2.py — All Consecutive

$ Input: Activity table: | player_id | device_id | event_date | games_played | |-----------|-----------|------------|-------------| | 1 | 1 | 2020-01-01 | 3 | | 1 | 1 | 2020-01-02 | 4 | | 2 | 2 | 2020-01-05 | 2 | | 2 | 2 | 2020-01-06 | 1 |

› Output: 1.00

💡 Note: Player 1 first login: 2020-01-01, second login: 2020-01-02 (consecutive ✓). Player 2 first login: 2020-01-05, second login: 2020-01-06 (consecutive ✓). Both players logged in consecutively: 2/2 = 1.00.

example_3.py — No Consecutive Logins

$ Input: Activity table: | player_id | device_id | event_date | games_played | |-----------|-----------|------------|-------------| | 1 | 1 | 2021-01-01 | 1 | | 2 | 2 | 2021-01-02 | 2 | | 3 | 3 | 2021-01-03 | 1 |

› Output: 0.00

💡 Note: Each player only has one login record, so none of them logged in on consecutive days after their first login. Result: 0/3 = 0.00.

Visualization

Tap to expand

Understanding the Visualization

Identify First Visits

Find each player's earliest login date using MIN() or ROW_NUMBER()

Check Next Day Returns

For each first visit, verify if the player logged in exactly one day later

Calculate Retention Rate

Divide consecutive-day players by total unique players

Format Result

Round to 2 decimal places for presentation

Key Takeaway

🎯 Key Insight: Use window functions to efficiently rank player logins, then self-join to identify consecutive day patterns. This approach scales well and leverages database optimizations for the best performance.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single pass with window function and join, optimized by database engine

⚡ Linearithmic

Space Complexity

O(n)

Temporary space for window function computation

⚡ Linearithmic Space

Constraints

1 ≤ Activity table rows ≤ 10⁴
1 ≤ player_id, device_id ≤ 10⁴
event_date is in YYYY-MM-DD format
0 ≤ games_played ≤ 1000
(player_id, event_date) is the primary key of the table

Asked in

f Meta 45 a Amazon 38 G Google 32 ⊞ Microsoft 28

The optimal solution uses window functions with ROW_NUMBER() to efficiently identify first logins, then self-joins to find consecutive day players. This approach achieves O(n log n) time complexity and is highly optimized by modern database engines. Key insight: rank each player's logins and join rank=1 with rank=2 records that are exactly 1 day apart.

Common Approaches

Approach	Time	Space	Notes
✓ Window Functions (Optimal)	O(n log n)	O(n)	Use ROW_NUMBER() window function to identify first logins and self-join efficiently
Two-Pass CTE Approach	O(n log n)	O(n)	Use Common Table Expressions to first identify first logins, then join with consecutive day data
Brute Force (Nested Subqueries)	O(n²)	O(1)	For each player, find their first date, then check if they have a record the next day

Window Functions (Optimal) — Algorithm Steps

Use ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) to rank logins
Self-join the table where rank=1 joins with records from next day
Count distinct players who have both first login and next-day login
Divide by total unique players and round to 2 decimals

Visualization

Tap to expand

Step-by-Step Walkthrough

Add Rankings

ROW_NUMBER() assigns ranks to each player's login dates

Self Join

Join rank=1 records with rank=2 records for same player

Date Check

Verify that rank=2 date is exactly 1 day after rank=1

Calculate

Count matches and divide by total players

Code -

solution.c — C

// Optimal SQL Query using Window Functions
char query[] = "\
WITH ranked_activity AS ( \
    SELECT player_id, event_date, \
           ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) as rn \
    FROM Activity \
) \
SELECT ROUND( \
    COUNT(DISTINCT a1.player_id) * 1.0 /  \
    (SELECT COUNT(DISTINCT player_id) FROM Activity), 2 \
) AS fraction \
FROM ranked_activity a1 \
JOIN ranked_activity a2 ON a1.player_id = a2.player_id \
WHERE a1.rn = 1  \
  AND a2.rn = 2  \
  AND DATEDIFF(a2.event_date, a1.event_date) = 1;";

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single pass with window function and join, optimized by database engine

⚡ Linearithmic

Space Complexity

O(n)

Temporary space for window function computation

⚡ Linearithmic Space

Constraints

1 ≤ Activity table rows ≤ 10⁴
1 ≤ player_id, device_id ≤ 10⁴
event_date is in YYYY-MM-DD format
0 ≤ games_played ≤ 1000
(player_id, event_date) is the primary key of the table

89.2K Views

High Frequency

~18 min Avg. Time

2.3K 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Window Functions (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler