Game Play Analysis V - Practice Coding Problems

Game Play Analysis V - Problem

Database Hard

Game Play Analysis V is an advanced SQL problem that challenges you to calculate player retention rates for a gaming platform.

You're given an Activity table that tracks player login sessions:

Column Name	Type
player_id	int
device_id	int
event_date	date
games_played	int

Key Concepts:
• Install date: The first day a player ever logged in
• Day one retention: Percentage of players who return the day after installing

Your Mission: For each install date, calculate:
1. Total number of players who installed on that date
2. Day one retention rate (rounded to 2 decimal places)

This metric is crucial for game companies to measure user engagement and optimize their onboarding experience!

Input & Output

example_1.sql — Basic Retention Analysis

$ 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: +------------+---------+----------------+ | install_date| installs| day1_retention | +------------+---------+----------------+ | 2016-03-01 | 2 | 0.50 | | 2016-07-03 | 1 | 0.00 | | 2017-06-25 | 1 | 0.00 | +------------+---------+----------------+

💡 Note: On 2016-03-01: 2 players installed (players 1 and 3). Only player 1 returned on 2016-03-02, so retention = 1/2 = 0.50. Other install dates had 0% retention as no players returned the next day.

example_2.sql — Perfect Retention

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

› Output: +------------+---------+----------------+ | install_date| installs| day1_retention | +------------+---------+----------------+ | 2020-01-01 | 2 | 1.00 | +------------+---------+----------------+

💡 Note: Both players installed on 2020-01-01 and both returned on 2020-01-02, resulting in perfect 100% day-1 retention.

example_3.sql — Zero Retention Edge Case

$ Input: Activity table: +----------+-----------+------------+--------------+ | player_id| device_id | event_date | games_played | +----------+-----------+------------+--------------+ | 1 | 1 | 2021-01-15 | 2 | | 2 | 2 | 2021-01-15 | 0 | | 3 | 3 | 2021-01-16 | 1 | +----------+-----------+------------+--------------+

› Output: +------------+---------+----------------+ | install_date| installs| day1_retention | +------------+---------+----------------+ | 2021-01-15 | 2 | 0.00 | | 2021-01-16 | 1 | 0.00 | +------------+---------+----------------+

💡 Note: Players installed but none returned the next day. All retention rates are 0.00, showing that even players with 0 games_played count as installs.

Constraints

1 ≤ player_id, device_id ≤ 10⁴
event_date is a valid date between 2016-02-28 and 2021-12-31
0 ≤ games_played ≤ 1000
The combination (player_id, event_date) is unique
A player's install date is their first recorded event_date

Visualization

Tap to expand

Understanding the Visualization

Identify Install Dates

Find each player's first login date using window functions

Match Next-Day Activity

Self-join to find players who returned the day after installing

Calculate Retention

Group by install date and compute retention percentage

Format Results

Round to 2 decimal places and order by install date

Key Takeaway

🎯 Key Insight: Use window functions to identify install dates efficiently, then self-join with date arithmetic to calculate retention rates in a single optimized query.

Asked in

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

The optimal solution uses window functions to identify install dates and self-joins to calculate day-1 retention efficiently. Key insights: use ROW_NUMBER() to find first logins, LEFT JOIN with date arithmetic to match next-day returns, and aggregate functions to compute retention rates in a single optimized query.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Subqueries for Each Player)	O(n²)	O(n)	Use nested subqueries to find install dates and check retention for each player individually
Window Function + Self Join (Optimal)	O(n log n)	O(n)	Use window functions to identify install dates and self-join to efficiently calculate retention

Brute Force (Subqueries for Each Player) — Algorithm Steps

Find all unique install dates using MIN(event_date) for each player
For each install date, count total players who installed
For each install date, count players who also logged in the next day
Calculate retention rate and round to 2 decimal places

Visualization

Tap to expand

Step-by-Step Walkthrough

Find Install Dates

Scan entire table to find each player's first login date

Count Installs

For each date, count how many players installed

Check Retention

For each install date, scan again to find next-day logins

Calculate Rate

Divide retained players by total installs

Code -

solution.c — C

// SQL solution using brute force approach
#include <stdio.h>

int main() {
    const char* sqlQuery = 
        "SELECT \n"
        "    install_dt AS install_date,\n"
        "    installs,\n"
        "    ROUND(\n"
        "        CASE \n"
        "            WHEN installs = 0 THEN 0.00\n"
        "            ELSE (\n"
        "                SELECT COUNT(DISTINCT a2.player_id) \n"
        "                FROM Activity a2 \n"
        "                WHERE a2.player_id IN (\n"
        "                    SELECT player_id \n"
        "                    FROM Activity \n"
        "                    WHERE event_date = install_dt \n"
        "                    AND (player_id, event_date) IN (\n"
        "                        SELECT player_id, MIN(event_date) \n"
        "                        FROM Activity \n"
        "                        GROUP BY player_id\n"
        "                    )\n"
        "                )\n"
        "                AND a2.event_date = DATE_ADD(install_dt, INTERVAL 1 DAY)\n"
        "            ) * 1.0 / installs\n"
        "        END, 2\n"
        "    ) AS day1_retention\n"
        "FROM (\n"
        "    SELECT \n"
        "        install_dt,\n"
        "        COUNT(DISTINCT player_id) AS installs\n"
        "    FROM (\n"
        "        SELECT \n"
        "            player_id,\n"
        "            MIN(event_date) AS install_dt\n"
        "        FROM Activity\n"
        "        GROUP BY player_id\n"
        "    ) install_dates\n"
        "    GROUP BY install_dt\n"
        ") install_stats\n"
        "ORDER BY install_date;";
    
    printf("%s\n", sqlQuery);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Multiple full table scans for each install date

⚠ Quadratic Growth

Space Complexity

O(n)

Temporary results for subqueries

⚡ Linearithmic Space

52.0K Views

High Frequency

~25 min Avg. Time

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

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Subqueries for Each Player) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler