Skip to main content
Talentd

Game Play Analysis II - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase4 min readAsked at: Amazon, Gsn Games
Practice this problem

Problem Statement

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
(player_id, event_date) is the primary key (combination of columns with unique values) of this table.
This table shows the activity of players of some games.
Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on someday using some device.

 

Write a solution to report the device that is first logged in for each player.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-05-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
Output: 
+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
| 1         | 2         |
| 2         | 3         |
| 3         | 1         |
+-----------+-----------+

Approach Overview

Problem Overview: The Activity table records player logins with player_id, device_id, and event_date. For each player, return the device they used on their first login date. The result should contain one row per player with the device corresponding to their earliest recorded activity.

Approach 1: Subquery (GROUP BY + Join) (Time: O(n log n), Space: O(n))

The key idea is to first determine each player’s earliest login date, then fetch the device used on that date. Use a subquery that groups by player_id and calculates MIN(event_date). Join this result back to the original Activity table on both player_id and the computed minimum date. The join ensures you retrieve the device_id associated with the first login. This approach works well across most SQL engines and clearly separates the aggregation step from the lookup step. It relies on standard SQL operations like GROUP BY, aggregation, and equality joins.

This method is common when solving aggregation problems in database queries. If indexes exist on player_id and event_date, the database optimizer can execute the grouping and join efficiently.

Approach 2: Window Function (ROW_NUMBER) (Time: O(n log n), Space: O(n))

Window functions provide a more direct way to rank rows within each player’s activity history. Use ROW_NUMBER() with PARTITION BY player_id and ORDER BY event_date. This assigns rank 1 to the earliest login for each player. Once the ranking is computed, filter the result where row_number = 1 to return only the first login device per player.

This approach avoids a self-join and keeps the logic in a single query block. Many engineers prefer it because the intent is clearer: rank events per player and pick the earliest one. Window functions are widely used in analytics-style SQL problems and appear frequently in interview questions involving ranking or per-group ordering in SQL and window functions.

Recommended for interviews: Both approaches are acceptable, but the window function solution demonstrates stronger SQL fluency and cleaner query structure. Starting with the subquery approach shows you understand aggregation and joins. Switching to the window function version shows you know modern SQL techniques used in production analytics workloads.

Approach 1: Subquery

We can use GROUP BY and MIN functions to find the first login date for each player, and then use a subquery with a composite key to find the first login device for each player.

Code

MySQL

MySQL

Try this approach in the editor →

Approach 2: Window Function

We can use the window function rank(), which assigns a rank to each login date for each player, and then select the rows with a rank of 1.

Code

MySQL

MySQL

Try this approach in the editor →

Complexity Comparison

ApproachComplexity
Subquery—
Window Function—

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Subquery (GROUP BY + Join)O(n log n)O(n)When using standard SQL patterns or when window functions are unavailable
Window Function (ROW_NUMBER)O(n log n)O(n)Cleaner solution when the database supports window functions

Video Solution

LeetCode 512: Game Play Analysis II [SQL] • Frederik Müller • 6,983 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is Game Play Analysis II easy or hard?
Game Play Analysis II is classified as an Easy database problem on LeetCode. It tests basic SQL skills such as grouping, joining tables, and using window functions to retrieve the earliest record within each group.
Game Play Analysis II Python/Java solution
This problem is designed for SQL rather than Python or Java because the data is stored in relational tables. The solution uses SQL constructs such as MIN(event_date), joins, or ROW_NUMBER() window functions to compute the earliest login device per player.
How to solve Game Play Analysis II in O(n)?
Pure O(n) behavior is possible in practice when indexes exist on player_id and event_date, allowing efficient scans and lookups. Conceptually, compute the minimum event_date per player and retrieve the corresponding device_id using either a join or a window function filter.
What is the best approach for Game Play Analysis II?
The window function approach using ROW_NUMBER() is typically the best solution. Partition rows by player_id and order by event_date to rank each login, then select the row with rank 1. This produces the device used on the first login with a single query and clear logic.
Is Game Play Analysis II asked at Google/Amazon/Meta?
Database query problems involving first events per user or earliest records frequently appear in SQL interviews at companies like Amazon, Google, and Meta. Variations often involve finding first purchases, first logins, or earliest transactions using aggregation or window functions.
What data structure is used in Game Play Analysis II?
The problem relies on relational database operations rather than traditional data structures. SQL features like GROUP BY aggregation, joins, and window functions act as the core mechanisms for grouping and ranking player activity records.
What is the time complexity of Game Play Analysis II?
Most SQL implementations run in about O(n log n) time because the database must sort rows by event_date within each player partition. Both the GROUP BY subquery and window function solutions have similar complexity depending on indexing and query planning.

Ready to solve this problem?

Practice Game Play Analysis II with our built-in code editor and test cases.

Practice on FleetCode
Previous

Game Play Analysis I

Next

Game Play Analysis III