Table: Logins
+----------------+----------+ | Column Name | Type | +----------------+----------+ | user_id | int | | time_stamp | datetime | +----------------+----------+ (user_id, time_stamp) is the primary key (combination of columns with unique values) for this table. Each row contains information about the login time for the user with ID user_id.
Write a solution to report the latest login for all users in the year 2020. Do not include the users who did not login in 2020.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Logins table: +---------+---------------------+ | user_id | time_stamp | +---------+---------------------+ | 6 | 2020-06-30 15:06:07 | | 6 | 2021-04-21 14:06:06 | | 6 | 2019-03-07 00:18:15 | | 8 | 2020-02-01 05:10:53 | | 8 | 2020-12-30 00:46:50 | | 2 | 2020-01-16 02:49:50 | | 2 | 2019-08-25 07:59:08 | | 14 | 2019-07-14 09:00:00 | | 14 | 2021-01-06 11:59:59 | +---------+---------------------+ Output: +---------+---------------------+ | user_id | last_stamp | +---------+---------------------+ | 6 | 2020-06-30 15:06:07 | | 8 | 2020-12-30 00:46:50 | | 2 | 2020-01-16 02:49:50 | +---------+---------------------+ Explanation: User 6 logged into their account 3 times but only once in 2020, so we include this login in the result table. User 8 logged into their account 2 times in 2020, once in February and once in December. We include only the latest one (December) in the result table. User 2 logged into their account 2 times but only once in 2020, so we include this login in the result table. User 14 did not login in 2020, so we do not include them in the result table.
In this approach, we use SQL queries to filter logins by the year 2020 and then use an aggregation function to find the latest login timestamp for each user. The process involves:
This code defines a SQL query that filters logins to those occurring in 2020 using WHERE condition with YEAR extraction and then finds the latest timestamp per user using GROUP BY and MAX() functions.
JavaScript
The SQL approach should have a time complexity of O(n) for scanning the table, where n is the number of rows, and a space complexity of O(u), where u is the number of unique users who logged in during 2020.
This approach involves loading the data and performing the filtering and aggregation in-memory using the language's data processing capabilities. We will iterate over the logins, filter out non-2020 entries, and use a data structure to track the latest login for each user.
This Python code iterates through the list of logins, filters out those from the year 2020, and updates a dictionary to track the latest timestamp for each user. The result is then constructed as a list of dictionaries for each user's latest login timestamp.
C#
The time complexity is O(n), with n as the number of logins, due to the single linear pass through the logins. Space complexity is O(u) based on the number of unique users who logged in during 2020.
| Approach | Complexity |
|---|---|
| Approach 1: SQL-based Filtering and Aggregation | The SQL approach should have a time complexity of O(n) for scanning the table, where n is the number of rows, and a space complexity of O(u), where u is the number of unique users who logged in during 2020. |
| Approach 2: Language-Specific In-memory Processing | The time complexity is O(n), with n as the number of logins, due to the single linear pass through the logins. Space complexity is O(u) based on the number of unique users who logged in during 2020. |
4 Leetcode Mistakes • Sahil & Sarra • 421,920 views views
Watch 9 more video solutions →Practice The Latest Login in 2020 with our built-in code editor and test cases.
Practice on FleetCode