Game Play Analysis IV

Game Play Analysis IV

What is this problem about?

The Game Play Analysis IV interview question is the most complex of the series. You need to report the fraction of players who logged in again exactly on the day after the day they first logged in, rounded to 2 decimal places. In marketing terms, you are calculating the "Day 1 Retention Rate."

Why is this asked in interviews?

Companies like Meta and Google use this Database interview pattern to test advanced metric calculation. It requires combining finding the "first event" (like in part I), performing date arithmetic (adding 1 day), and then checking for existence using a JOIN or IN clause. It evaluates your ability to build a multi-step query to derive a high-level business KPI.

Algorithmic pattern used

This problem requires a Subquery and Date Arithmetic.

1. Find First Login: Create a CTE or subquery that finds the player_id and MIN(event_date) (their first login).
2. Self-Join / Filter: Join this subquery back to the Activity table. Look for a row where the player_id matches AND the Activity.event_date is exactly one day after the first_login date. Use functions like DATE_ADD() or DATEDIFF().
3. Calculate Fraction: Count the distinct players from step 2 (those who retained) and divide by the total number of distinct players in the entire table.

Example explanation

Players and their first logins:

• Player 1: First login 2016-03-01. Did they log in on 2016-03-02? Yes. (Retained).
• Player 2: First login 2017-06-25. Did they log in on 2017-06-26? No.
• Player 3: First login 2016-03-01. Did they log in on 2016-03-02? No.

Total players = 3. Retained players = 1. Fraction = $1 / 3 = 0.33$.

Common mistakes candidates make

• Counting any consecutive days: The problem asks specifically for the day after the first login. Checking if a player logged in on any two consecutive days is incorrect.
• Date Math Syntax: Using database-specific date math (like date + 1) that might fail in other environments. Standardizing on DATE_ADD(date, INTERVAL 1 DAY) or similar explicit functions is safer.
• Integer Division: Forgetting to cast the counts to decimal/float before dividing, resulting in 0.00.

Interview preparation tip

"Retention" is a massive keyword in data engineering. If you see it, you will almost certainly need to find a user's initial cohort date (their first action) and then join that back to the event log to see if they performed an action $X$ days later.