The Latest Login in 2020 - Solution & Explanation

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.