Watch 10 video solutions for Game Play Analysis I, a easy level problem involving Database. This walkthrough by Frederik Müller has 7,365 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
| 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 |
