Watch 7 video solutions for The Latest Login in 2020, a easy level problem involving Database. This walkthrough by Everyday Data Science has 5,286 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
Problem Overview: You are given a Logins table containing user_id and time_stamp. The task is to return the latest login time for each user that occurred in the year 2020. If a user logged in multiple times during 2020, only the most recent timestamp should appear in the result.
Approach 1: SQL-based Filtering and Aggregation (O(n) time, O(1) extra space)
This approach relies on database filtering and aggregation. First filter rows where the year of time_stamp equals 2020 using YEAR(time_stamp) = 2020 or a date range condition. Then group records by user_id and compute the maximum timestamp using MAX(time_stamp). The database engine performs the scan and aggregation efficiently, returning one row per user. This is the cleanest solution when working directly with relational data and aligns with standard database query patterns.
Approach 2: Language-Specific In-memory Processing (O(n) time, O(u) space)
If the login records are processed in application code rather than SQL, load the rows and iterate once through the dataset. During iteration, ignore entries whose year is not 2020. Maintain a hash map keyed by user_id that stores the most recent timestamp seen so far. For every valid login, compare the current timestamp with the stored value and update it if the new one is later. This approach mirrors the SQL aggregation logic but performs it using a hash map in languages like Python or C#. It works well when data already exists in memory or when processing logs outside a database system.
Recommended for interviews: The SQL filtering and aggregation solution is the expected answer for a database problem. Interviewers want to see correct use of WHERE filtering and GROUP BY with MAX(). Implementing the same logic with a hash map in application code demonstrates understanding of aggregation mechanics, but SQL is the most concise and idiomatic solution.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL Filtering + GROUP BY Aggregation | O(n) | O(1) | When solving directly in SQL or database interview problems |
| In-memory Hash Map Processing | O(n) | O(u) | When login data is processed in application code or log-processing systems |
