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 | +-----------+-------------+
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.
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.
Java
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.
| 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. |
LeetCode 550: Game Play Analysis IV [SQL] • Frederik Müller • 10,657 views views
Watch 9 more video solutions →Practice Game Play Analysis I with our built-in code editor and test cases.
Practice on FleetCode