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 find the first login date 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 | first_login | +-----------+-------------+ | 1 | 2016-03-01 | | 2 | 2017-06-25 | | 3 | 2016-03-02 | +-----------+-------------+
Problem Overview: You are given an Activity table that records each player's login activity with player_id, device_id, event_date, and games_played. The task is to return the first login date for every player. In other words, determine the earliest event_date recorded for each player_id.
Approach 1: SQL Aggregation with GROUP BY (O(n) time, O(k) space)
The most direct solution uses SQL aggregation. Group rows by player_id and compute the minimum event_date within each group. The MIN() aggregate function returns the earliest login date, which represents the player's first appearance in the system. The database engine scans the table once and aggregates results per player, making the time complexity O(n) where n is the number of rows in the Activity table. Space complexity is O(k), where k is the number of unique players stored in the result set. This approach is standard for problems involving per-entity summaries in database queries and is the expected solution in interviews and SQL assessments.
Approach 2: Data Processing with Collections (O(n) time, O(k) space)
If the data is processed in an application layer instead of directly in SQL, you can use a hash-based collection to track each player's earliest login. Iterate through all records and maintain a map where the key is player_id and the value is the minimum event_date seen so far. For each record, check the existing value and update it if the current date is earlier. Hash lookups and updates run in constant time, resulting in overall O(n) time complexity. The map stores one entry per player, so space complexity is O(k). This approach appears frequently when transforming tabular data using hash tables or data processing pipelines in languages like Python or Java.
Recommended for interviews: The SQL GROUP BY with MIN() is the canonical answer. Interviewers expect you to recognize that the problem is simply computing the earliest date per player. Demonstrating the collection-based approach in Python or Java shows you understand how the same aggregation logic works outside a database system.
This approach leverages SQL aggregate functions to determine the first login date for each player. We use the MIN function to get the earliest event_date for each player_id. This is accomplished by grouping the results based on the player_id field.
The SQL query selects the smallest event_date for each player_id, yielding the first login date. The GROUP BY clause groups the data based on player_id, enabling the MIN function to operate within each group independently.
SQL
Time complexity: O(n) due to a single pass through the Activity table.
Space complexity: O(k), where k is the number of unique player_ids.
This approach involves loading the data into a data structure that optimizes lookup times, such as a dictionary or a map, and then iterating over the list to find the earliest event_date for each player_id. This functionally mimics the behavior of the SQL approach but executed in a programming environment rather than within a database.
This Python function processes a list of tuples each containing the fields from the Activity table. It iterates through the records, updating a map that tracks the earliest event_date for each player_id. After processing all records, it outputs a list of tuples with the player_id and their respective first_login date.
Time complexity: O(n), with a single traversal of the records list.
Space complexity: O(k), where k is the number of unique player_ids.
We can use GROUP BY to group the player_id and then take the minimum event_date in each group as the date when the player first logged into the platform.
| Approach | Complexity |
|---|---|
| SQL Query Approach | Time complexity: O(n) due to a single pass through the Activity table. |
| Data Processing with Collections | Time complexity: O(n), with a single traversal of the records list. |
| Group By + Min Function | — |
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL Aggregation (GROUP BY + MIN) | O(n) | O(k) | When solving directly in SQL or working with relational databases |
| Collections / Hash Map Tracking | O(n) | O(k) | When processing records in application code using Python or Java |
LeetCode 511: Game Play Analysis I [SQL] • Frederik Müller • 7,365 views views
Watch 9 more video solutions →Practice Game Play Analysis I with our built-in code editor and test cases.
Practice on FleetCode