The Latest Login in 2020 - Problem
You are given a table Logins that contains information about user login times.
Write a SQL query to find the latest login for each user in the year 2020. Do not include users who did not login in 2020.
Return the result table in any order.
Table Schema
Logins
| Column Name | Type | Description |
|---|---|---|
user_id
PK
|
int | User identifier |
time_stamp
PK
|
datetime | Login timestamp |
Primary Key: (user_id, time_stamp)
Note: Each row represents a login event for a specific user
Input & Output
Example 1 — Multiple Users with Multiple Logins
Input Table:
| user_id | time_stamp |
|---|---|
| 1 | 2020-01-01 10:00:00 |
| 1 | 2020-12-30 17:30:00 |
| 2 | 2020-10-12 14:00:00 |
| 3 | 2019-06-30 09:00:00 |
| 3 | 2021-04-21 15:00:00 |
Output:
| user_id | last_stamp |
|---|---|
| 1 | 2020-12-30 |
| 2 | 2020-10-12 |
💡 Note:
User 1 had two logins in 2020, we return the latest one (2020-12-30). User 2 had one login in 2020. User 3 had no logins in 2020, so they are excluded from the result.
Example 2 — Single Login Per User
Input Table:
| user_id | time_stamp |
|---|---|
| 5 | 2020-03-15 08:30:00 |
| 7 | 2020-07-20 12:45:00 |
Output:
| user_id | last_stamp |
|---|---|
| 5 | 2020-03-15 |
| 7 | 2020-07-20 |
💡 Note:
Each user has only one login in 2020, so that single login is their latest (and only) login for the year.
Example 3 — No 2020 Logins
Input Table:
| user_id | time_stamp |
|---|---|
| 10 | 2019-12-31 23:59:59 |
| 11 | 2021-01-01 00:00:01 |
Output:
| user_id | last_stamp |
|---|
💡 Note:
No users had any logins during 2020, so the result is empty. All logins were either before 2020 or after 2020.
Constraints
-
1 ≤ user_id ≤ 10^4 -
time_stampis a valid datetime - The table may contain multiple login records for the same user
Visualization
Tap to expand
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code