Abstract / Summary

Given player actions and game events, determine which players did not reach the last level in a game.

Note

The full problem description, including helpful examples, can be found on LeetCode.

This problem is ranked as

and has an acceptance rate of about 41% (1+ million submissions).

Typical patterns to solve this problem:

• Database

Every single line of code was written by me, 100% from scratch, between August 2023 and August 2024.
However, two years later I set up this website and used various AI tools to generate most of the walkthrough / explanations below.

See this dedicated page for more details.

Overview

The query calculates the proportion of distinct players whose activity on a given day follows a day-after-first-activity pattern. It first determines each player's earliest activity date, then counts how many players have an activity exactly one day after that earliest date, and finally divides that count by the total number of distinct players in the Activity table. The result is rounded to two decimal places and returned as fraction.

Key Steps

The first step isolates the first activity date for every player. This is achieved with a common table expression named started that groups rows by player_id and selects the minimum event_date.

WITH started AS (
    SELECT player_id,
           MIN(event_date) AS event_date
    FROM Activity
    GROUP BY player_id
)

The second step counts the players who have an activity exactly one day after their first activity. It joins the started CTE with the original Activity table on player_id and applies a date comparison that subtracts one day from the activity date, matching it to the stored first date.

SELECT COUNT(DISTINCT a.player_id) AS player_id
FROM started s, Activity a
WHERE s.player_id = a.player_id
  AND s.event_date = DATE_SUB(a.event_date, INTERVAL 1 DAY)

The third step obtains the total number of distinct players present in the Activity table, independent of any date condition.

SELECT COUNT(DISTINCT player_id)
FROM Activity

The final step combines the two counts using division, wraps the expression with ROUND(..., 2) to keep two decimal places, and aliases the result as fraction.

SELECT ROUND(
         (SELECT COUNT(DISTINCT a.player_id)
            FROM started s, Activity a
           WHERE s.player_id = a.player_id
             AND s.event_date = DATE_SUB(a.event_date, INTERVAL 1 DAY))
         /
         (SELECT COUNT(DISTINCT player_id)
            FROM Activity),
         2) AS fraction

Edge Cases Handled

The query gracefully handles players who have only a single activity record. Since the join condition requires an activity that occurs exactly one day after the first activity, such players are automatically excluded from the numerator, ensuring the fraction reflects only those who meet the day-after-first-activity criterion. It also accommodates scenarios where multiple activities occur on the same day; the MIN(event_date) function guarantees that the earliest date is selected regardless of duplicate entries. If a player’s activity spans non-consecutive days, the date subtraction logic prevents false positives because only a precise one-day offset satisfies the condition.

Performance Optimizations

The use of a common table expression (started) materializes the minimal event date per player once, avoiding repeated aggregation during the join. By joining on player_id and applying the date comparison directly in the WHERE clause, the query leverages any existing index on player_id and event_date, reducing the search space for matching rows. The distinct counts are computed with COUNT(DISTINCT ...), which can be efficiently executed when the underlying columns are indexed, minimizing the overhead of duplicate elimination. The overall structure keeps the data flow linear, preventing unnecessary subqueries or nested loops that could degrade execution speed.

Imagine a giant toy box filled with records of every game played by different kids. The first part of the code acts like a detective that looks at all the records to find the very first day each kid decided to play a game. It makes a little list of these starting days.

Once that list is ready, the rest of the code plays a game of tag. It looks through the toy box to see which kids came back to play again exactly one day after that first starting day. It counts how many kids were dedicated enough to return for that second day.

After finding that number, it divides it by the total number of kids who ever played. This helps find out what portion of the players are loyal enough to come back the very next day. Finally, the code rounds that number to make it tidy and easy to read, just like how you might round off the number of cookies on a plate.

Several LLMs ranked my code quality on a scale from 1 (very poor) to 10 (outstanding):

not analyzed yet

I wrote a basic crawler to find other solutions to that problem.
Obviously, I did that after I solved the problem myself.

In general, AlgoMonster and WalkCCC solutions are high-quality and always worth a look.
I haven't checked most of the other solutions, but they might be helpful as well.

And of course, the LeetCode forum is always a good place (or even the best) to look for alternative approaches.

(click on a column header to sort the table)

	author
author	solutions
www	AlgoMonster		C++	Java	Python	TS
	WalkCCC	SQL
	leetcode.ca	SQL			Python
	LeetcodeHelp		C++	Java	Python
	Progiez		C++	Java	Python
github	everthis	SQL
	suxrobGM	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	Python, SQL
LeetcodeHelp	C++, Java, Python
Progiez	C++, Java, Python
everthis	SQL
suxrobGM	SQL

There are a few extra pages giving you some insights into my setup:

• Tech Stack - my development environment and tools
• AI - how AI helped me create this website (NOT solving the problems)
• Progress - progression over time
• Quality - code quality