The Latest Login in 2020 - Practice Coding Problems

The Latest Login in 2020 - Problem

Database Easy

You're tasked with analyzing user login data for a busy web application. Given a Logins table that tracks every user login with timestamps, you need to find the most recent login for each user specifically during the year 2020.

The Logins table structure:

Column Name	Type
user_id	int
time_stamp	datetime

Important: Only include users who actually logged in during 2020 - ignore users who didn't log in that year at all.

Goal: Write a SQL query that returns each user's latest login timestamp from 2020. The results can be in any order.

Input & Output

example_1.sql — Basic Example

$ Input: Logins table: | user_id | time_stamp | |---------|--------------------| | 6 | 2020-06-30 15:06:07| | 6 | 2021-04-21 14:03: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|

› Output: | user_id | last_login | |---------|--------------------| | 6 | 2020-06-30 15:06:07| | 8 | 2020-12-30 00:46:50| | 2 | 2020-01-16 02:49:50|

💡 Note: User 6 logged in twice in 2020 (June 30) and other years, so we take their latest 2020 login. User 8 had two 2020 logins, we take December 30. User 2 had one 2020 login in January. All other login years are ignored.

example_2.sql — Single Login Per User

$ Input: Logins table: | user_id | time_stamp | |---------|--------------------| | 1 | 2020-03-15 10:30:00| | 2 | 2020-07-22 14:15:30| | 3 | 2020-11-05 09:45:12| | 1 | 2019-12-01 08:00:00| | 2 | 2021-01-10 16:30:00|

› Output: | user_id | last_login | |---------|--------------------| | 1 | 2020-03-15 10:30:00| | 2 | 2020-07-22 14:15:30| | 3 | 2020-11-05 09:45:12|

💡 Note: Each user has exactly one login in 2020, so those are their latest (and only) 2020 logins. User 1's 2019 login and User 2's 2021 login are excluded.

example_3.sql — No 2020 Logins Edge Case

$ Input: Logins table: | user_id | time_stamp | |---------|--------------------| | 1 | 2019-12-31 23:59:59| | 1 | 2021-01-01 00:00:01| | 2 | 2018-05-15 12:30:00| | 2 | 2022-03-10 14:20:15|

› Output: | user_id | last_login | |---------|------------| | | |

💡 Note: No users logged in during 2020, so the result set is empty. User 1 logged in on Dec 31, 2019 and Jan 1, 2021, but neither is in 2020. User 2 logged in 2018 and 2022, also outside our target year.

Constraints

1 ≤ Number of login records ≤ 10⁴
Each (user_id, time_stamp) combination is unique
time_stamp is a valid datetime value
user_id is a positive integer

Visualization

Tap to expand

Understanding the Visualization

Filter by Year

Remove all login records that aren't from 2020, like filtering mail by postmark year

Group by User

Organize remaining records by user_id, like sorting mail into piles by recipient

Find Latest in Each Group

For each user's pile, pick the record with the maximum timestamp

Return Results

Output each user with their latest 2020 login timestamp

Key Takeaway

🎯 Key Insight: GROUP BY with MAX() leverages database optimization to process each record exactly once, eliminating the need for nested queries or multiple passes through the data.

Asked in

a Amazon 45 f Meta 35 G Google 30 ⊞ Microsoft 25

The optimal solution uses GROUP BY user_id combined with MAX(time_stamp) to find each user's latest login in 2020. First filter records with WHERE YEAR(time_stamp) = 2020, then group by user and select the maximum timestamp per group. This achieves O(n) time complexity in a single pass.

Common Approaches

Approach	Time	Space	Notes
✓ GROUP BY with MAX (Optimal)	O(n)	O(u)	Filter 2020 records first, then group by user_id and find MAX timestamp
Brute Force (Nested Queries)	O(n²)	O(n)	Use correlated subqueries to find max timestamp for each user individually

GROUP BY with MAX (Optimal) — Algorithm Steps

Filter all records to only include year 2020 using WHERE clause
Group the filtered records by user_id
Apply MAX() function to find the latest time_stamp in each group
Return user_id and their maximum timestamp

Visualization

Tap to expand

Step-by-Step Walkthrough

Filter 2020

WHERE clause filters to 2020 records only

Group Users

GROUP BY organizes records by user_id

Find MAX

MAX() function finds latest timestamp per group

Return Results

Each user appears once with their latest login

Code -

solution.c — C

// Optimal SQL query strings for C implementation
#include <stdio.h>

const char* get_optimal_query() {
    static const char query[] = 
        "SELECT user_id, MAX(time_stamp) as last_login "
        "FROM Logins "
        "WHERE time_stamp >= '2020-01-01' "
        "  AND time_stamp < '2021-01-01' "
        "GROUP BY user_id;";
    return query;
}

const char* get_year_query() {
    static const char query[] =
        "SELECT user_id, MAX(time_stamp) as last_login "
        "FROM Logins "
        "WHERE YEAR(time_stamp) = 2020 "
        "GROUP BY user_id;";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through 2020 records with efficient grouping

✓ Linear Growth

Space Complexity

O(u)

Space proportional to number of unique users in 2020

✓ Linear Space

35.2K Views

High Frequency

~8 min Avg. Time

1.5K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

GROUP BY with MAX (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler