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 | +-----------+-----------+
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.
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.
MySQL
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.
MySQL
| Approach | Complexity |
|---|---|
| Subquery | — |
| Window Function | — |
| Approach | Time | Space | When 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 |
LeetCode 512: Game Play Analysis II [SQL] • Frederik Müller • 6,905 views views
Watch 8 more video solutions →Practice Game Play Analysis II with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor