[LeetCode][MySQL] 1890. The Latest Login in 2020

Easy 🔗1890. The Latest Login in 2020

📝문제 요약

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.

✏️문제 풀이

SELECT

SELECT user_id, MAX(time_stamp) AS last_stamp

• user_id : 유저 ID
• last_stamp : 마지막 접속 시간
  • MAX(time_stamp) : 접속시간의 최대값. 즉 가장 최근에 로그인한 기록

FROM

FROM Logins

• Logins 테이블에서 데이터를 가져옴

WHERE

WHERE YEAR(time_stamp) = 2020

• 2020년 최신 로그인 정보가 필요하므로 time_stamp의 년도(YEAR)가 2020년인 데이터만 필터링

GROUP BY

GROUP BY user_id

• user_id 를 기준으로 그룹화

💯제출 코드

SELECT user_id, MAX(time_stamp) AS last_stamp
FROM Logins
WHERE YEAR(time_stamp) = 2020
GROUP BY user_id