1890. The Latest Login in 2020 - Solution

The problem

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.

Approach overview

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 1: Approach 1: SQL-based Filtering and Aggregation

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:

Filtering the logins to only include entries where the year part of the 'time_stamp' column is 2020.
Grouping the remaining entries by 'user_id'.
Using SQL's MAX() function to obtain the latest login date for each user in 2020.

Python

# Python has no direct query execution like SQL, but we can conceptualize it:
query = '''
SELECT user_id, MAX(time_stamp) AS last_stamp
FROM Logins
WHERE YEAR(time_stamp) = 2020
GROUP BY user_id
'''

JavaScript

// JavaScript typically does not process SQL, but this represents the concept:
let query = '
SELECT user_id, MAX(time_stamp) AS last_stamp
FROM Logins
WHERE YEAR(time_stamp) = 2020
GROUP BY user_id
';

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.

Complexity

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.

Try this approach in the editor

Approach 2: Approach 2: Language-Specific In-memory Processing

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.

Python

from datetime import datetime

logins = [
    {'user_id': 6, 'time_stamp': '2020-06-30 15:06:07'},
    {'user_id': 6, 'time_stamp': '2021-04-21 14:06:06'},
    {'user_id': 6, 'time_stamp': '2019-03-07 00:18:15'},
    {'user_id': 8, 'time_stamp': '2020-02-01 05:10:53'},
    {'user_id': 8, 'time_stamp': '2020-12-30 00:46:50'},
    {'user_id': 2, 'time_stamp': '2020-01-16 02:49:50'},
    {'user_id': 2, 'time_stamp': '2019-08-25 07:59:08'},
    {'user_id': 14, 'time_stamp': '2019-07-14 09:00:00'},
    {'user_id': 14, 'time_stamp': '2021-01-06 11:59:59'}
]

latest_logins = {}

for login in logins:
    ts = datetime.strptime(login['time_stamp'], '%Y-%m-%d %H:%M:%S')
    if ts.year == 2020:
        if login['user_id'] not in latest_logins or latest_logins[login['user_id']] < ts:
            latest_logins[login['user_id']] = ts

result = [{'user_id': user, 'last_stamp': latest_logins[user]} for user in latest_logins]

using System;
using System.Collections.Generic;

public class LatestLogin
{
    public static void Main()
    {
        var logins = new List<(int user_id, DateTime time_stamp)>
        {
            (6, DateTime.Parse("2020-06-30 15:06:07")),
            (6, DateTime.Parse("2021-04-21 14:06:06")),
            (6, DateTime.Parse("2019-03-07 00:18:15")),
            (8, DateTime.Parse("2020-02-01 05:10:53")),
            (8, DateTime.Parse("2020-12-30 00:46:50")),
            (2, DateTime.Parse("2020-01-16 02:49:50")),
            (2, DateTime.Parse("2019-08-25 07:59:08")),
            (14, DateTime.Parse("2019-07-14 09:00:00")),
            (14, DateTime.Parse("2021-01-06 11:59:59"))
        };

        var latestLogins = new Dictionary<int, DateTime>();

        foreach (var login in logins)
        {
            if (login.time_stamp.Year == 2020)
            {
                if (!latestLogins.ContainsKey(login.user_id) || latestLogins[login.user_id] < login.time_stamp)
                {
                    latestLogins[login.user_id] = login.time_stamp;
                }
            }
        }

        foreach (var kvp in latestLogins)
        {
            Console.WriteLine($"User: {kvp.Key}, Last Login: {kvp.Value}");
        }
    }
}

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.

Complexity

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.

Try this approach in the editor

Approach 3: Group By + Max Function

We can first filter out the login records in 2020, and then group by user_id, and use the max function to calculate the maximum login time for each user.

MySQL

# Write your MySQL query statement below
SELECT user_id, MAX(time_stamp) AS last_stamp
FROM Logins
WHERE YEAR(time_stamp) = 2020
GROUP BY 1;

Try this approach in the editor

Complexity comparison

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.
Group By + Max Function	–

Complexity analysis

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

Frequently asked questions

Is The Latest Login in 2020 easy or hard?

The Latest Login in 2020 is categorized as an Easy problem. It mainly tests basic SQL filtering and aggregation skills using GROUP BY and MAX, which are fundamental operations in database interview questions.

The Latest Login in 2020 Python/Java solution

In Python or C#, iterate through login records, filter timestamps belonging to 2020, and store the maximum timestamp for each user in a dictionary. SQL solutions typically use SELECT user_id, MAX(time_stamp) with GROUP BY after filtering rows for the 2020 date range.

How to solve The Latest Login in 2020 in O(n)?

Filter login records where the timestamp falls within 2020, then compute the maximum timestamp for each user. In SQL this is done with WHERE conditions and GROUP BY user_id using MAX(time_stamp). In code, iterate through the records and maintain a hash map storing the latest timestamp per user.

What is the best approach for The Latest Login in 2020?

The best approach is filtering rows from 2020 and using SQL aggregation with GROUP BY user_id and MAX(time_stamp). This returns the latest login for each user in a single query. The database engine performs the scan and aggregation efficiently with O(n) time complexity.

Is The Latest Login in 2020 asked at Google/Amazon/Meta?

Database aggregation and filtering questions like this commonly appear in SQL interview rounds across companies including Amazon, Meta, and Google. The focus is usually on correct use of GROUP BY, date filtering, and aggregate functions.

What data structure is used in The Latest Login in 2020?

The SQL version relies on relational database aggregation rather than explicit data structures. When implemented in application code, a hash map (dictionary) keyed by user_id is used to track the latest timestamp for each user.

What is the time complexity of The Latest Login in 2020?

The time complexity is O(n) where n is the number of login records. The database or program scans each row once to filter entries from 2020 and compute the maximum timestamp per user. Space complexity is O(1) in SQL or O(u) in application code where u is the number of unique users.

The Latest Login in 2020 solution and explanation

The problem

Approach overview

Approach 1: Approach 1: SQL-based Filtering and Aggregation

Approach 2: Approach 2: Language-Specific In-memory Processing

Approach 3: Group By + Max Function

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

The Latest Login in 2020 solution and explanation

The problem

Approach overview

Approach 1: Approach 1: SQL-based Filtering and Aggregation

Approach 2: Approach 2: Language-Specific In-memory Processing

Approach 3: Group By + Max Function

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Related problems