Game Play Analysis V

Game Play Analysis V

What is this problem about?

The Game Play Analysis V interview question is the final and hardest iteration of the player retention SQL series. You are asked to report for each "install date" (the date when a player first logged in), the number of players that installed on that date, and the "Day 1 retention rate." The retention rate is the number of players who logged in the day after their install date, divided by the number of players who installed on that date. The result should be rounded to 2 decimal places.

Why is this asked in interviews?

Companies like GSN Games use this Database coding problem to evaluate advanced SQL skills. It tests a candidate's ability to combine cohort definition (finding the first login date per user) with self-joins and conditional aggregation. It evaluates whether you can calculate ratios within specific groupings (cohorts) rather than just a single global ratio like in Analysis IV.

Algorithmic pattern used

This problem relies on a CTE (Common Table Expression), Self-Join, and Conditional Aggregation.

1. Define Cohorts: Create a CTE (InstallDates) that finds player_id and their MIN(event_date) as install_dt.
2. Join: LEFT JOIN the Activity table to this CTE. The join condition should be the player_id matching AND the Activity.event_date being exactly 1 day after the install_dt.
3. Aggregate: GROUP BY install_dt.
4. Count:
   • Total installs for that day: COUNT(DISTINCT player_id) from the CTE.
   • Retained installs: COUNT(Activity.player_id) (since the LEFT JOIN will have NULLs if they didn't log in on day 2).
5. Calculate Ratio: Retained / Total Installs.

Example explanation

Activity:

• P1: Mar 1 (Install), Mar 2 (Retained)
• P2: Mar 1 (Install), Mar 3 (Not Retained on Day 1)
• P3: Mar 2 (Install), Mar 3 (Retained)

Grouping by Install Date:

• Mar 1 Cohort: P1, P2. (Total = 2). Retained = P1 (Total = 1). Ratio = 1/2 = 0.50.
• Mar 2 Cohort: P3. (Total = 1). Retained = P3 (Total = 1). Ratio = 1/1 = 1.00.

Common mistakes candidates make

• INNER JOIN instead of LEFT JOIN: If you use an INNER JOIN to find retained players, you will completely exclude install dates where zero players were retained, which is incorrect.
• Counting Total Players Wrong: Counting the rows in the joined table instead of counting distinct player_ids from the initial cohort definition.
• Integer Division: Forgetting to cast the counts or multiply by 1.0 before dividing, resulting in 0.00.

Interview preparation tip

Cohort analysis is a staple of data science interviews. Practice structuring your queries cleanly using CTEs. Step 1: Define the cohort (who and when). Step 2: Join the activity you want to measure. Step 3: Aggregate by the cohort dimension.