Game Play Analysis IV | LeetCode 550 | Crack SQL Interviews in 50 Qs

Welcome to Question 22 of the 50-question SQL interview series. In this chapter, we explore Game Play Analysis IV (LeetCode 550), a classic medium-level database problem that tests your ability to handle dates, identify sequential user behavior, and compute precise fractional metrics using subqueries and aggregate functions.

1. Why Do We Need This? (Problem Statement & Status Quo)

In product analytics, user retention is a critical key performance indicator (KPI). Specifically, product managers often want to know what percentage of users come back to play or use an app on the very next day after their first login.

• The Objective: Write an SQL query to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places.
• Mathematical Definition:

2. Core Mental Model & Real-Life Analogy

Real-Life Analogy: The Fitness Club Sign-Up Challenge

Imagine a local gym hosting a 2-day promotional challenge. They want to track member habit retention:

1. First-Time Visitors: Every member has a strict "Day 1" (their absolute registration/first visit date).
2. The Streak Check: The gym manager asks, "Did this member show up again on Day 1 + 1 (the exact next calendar day)?"
3. The Final Ratio: If 100 people signed up initially, and 33 of them returned the very next day, the retention fraction is 33 / 100 = 0.33.

In SQL, we replicate this by first isolating each player's First Login Date, checking if a record exists for First Login + 1 day, and finally dividing that count by the total distinct player population.

3. Database Schema & Sample Data

The problem provides a single table named Activity with four columns:

Note: The primary key for this table is the composite key (player_id, event_date), meaning a single player can have multiple rows across different dates.

Example Walkthrough Table

Let's examine a sample dataset:

Step-by-Step Evaluation:

1. Player 1: First logged in on 2016-03-01. Did they log in on 2016-03-02? Yes, row 2 confirms this. -> Valid Match (1)
2. Player 2: First logged in on 2016-06-25. Did they log in on 2016-06-26? No records exist for the next day. -> Invalid (0)
3. Player 3: First logged in on 2016-03-02. Did they log in on 2016-03-03? No, their next login was 2016-07-03. -> Invalid (0)

• Total Unique Players: 3 (1, 2, 3)
• Players Meeting Condition: 1 (Player 1)
• Calculated Fraction: 1 / 3 = 0.33

4. Step-by-Step Query Evolution

To solve this efficiently, we will build our query in two logical layers:

1. The Subquery: Find each player's first login date and filter for players who logged in on the consecutive day.
2. The Outer Query: Compute the ratio of matching players against the total unique player count.

5. Building the Subquery (Finding First Login & Consecutive Dates)

First, we group our Activity table by player_id to establish each user's baseline initial login date using the MIN() aggregate function.

SELECT 
    player_id, 
    MIN(event_date) AS first_login
FROM Activity
GROUP BY player_id;

Next, to check if the player logged in on the very next day, we use MySQL's date arithmetic (DATE_SUB or interval checks). We want to verify if a record exists in the Activity table where:

Expressed using MySQL date subtraction syntax:

WHERE (player_id, DATE_SUB(event_date, INTERVAL 1 DAY)) 
      IN (
          SELECT player_id, MIN(event_date) 
          FROM Activity 
          GROUP BY player_id
      )

6. Assembling the Complete Production Query

Combining our subquery logic with the outer aggregation, we arrive at the final solution. Notice how we compute the numerator (retained players) and denominator (total players) to form the exact fraction.

SELECT 
    ROUND(
        COUNT(DISTINCT player_id) / 
        (SELECT COUNT(DISTINCT player_id) FROM Activity), 
        2
    ) AS fraction
FROM Activity
WHERE (player_id, DATE_SUB(event_date, INTERVAL 1 DAY)) IN (
    SELECT 
        player_id, 
        MIN(event_date) AS first_login
    FROM Activity
    GROUP BY player_id
);

7. Execution Flow Walkthrough

Let's trace how the database engine executes this query step by step:

8. Real-World Practical Pitfalls & Edge Cases

When deploying date-based analytics queries in production systems, keep these edge cases in mind:

• Timezone Discrepancies: Database servers often store timestamps in UTC. If user logins are recorded in local times, DATE_SUB might miscalculate boundaries due to offset shifts. Always normalize event timestamps to date types (DATE(event_date)) when doing day-level comparisons.
• Denominator Traps: If you omit the inner SELECT COUNT(DISTINCT player_id) FROM Activity wrapper for the denominator and simply divide by a local count, your query will return incorrect ratios (such as 1 / 1 = 1.00) because the outer WHERE clause has already filtered out non-matching players.
• Indexing Strategy: To ensure high performance on large tables with millions of rows, ensure composite indexes exist on (player_id, event_date).

Summary and Key Takeaways

• Fraction Metrics: Computing ratios in SQL requires dividing a filtered numerator subset by an unfiltered global denominator.
• First Login Isolation: Use MIN(event_date) paired with GROUP BY player_id to reliably pinpoint when a user started their journey.
• Date Arithmetic: Utilize interval functions like DATE_SUB(date, INTERVAL 1 DAY) or DATE_ADD to check for consecutive calendar sequences.
• Tuple Membership: Leveraging SQL tuple matching (col1, col2) IN (Subquery) is an efficient way to correlate rows against aggregated milestones.
• Rounding Output: Always wrap fractional divisions in ROUND(val, 2) to meet explicit UI or API precision requirements.