LeetCode MySQL-1890. The Latest Login in 2020

    AllenLi
    LeetCode MySQL

    Write an SQL query 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.

    寫一個 SQL Query來報告使用者最後一次在2020登入的時間，且不包含未在2020登入過的使用者。
可任意排序結果。

    Table: Logins

    +----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
+----------------+----------+
(user_id, time_stamp) is the primary key for this table.
Each row contains information about the login time for the user with ID user_id.

    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.

    Solution:
    1. 選擇標題 user_id。
    2. 選擇標題 last_stamp 為最大的 time_stamp 時間。
    3. 來自 Logins 的 table。
    4. 選取 time_stamp 前四碼為 ‘2020’。
    5. 以 user_id 為群組。

    Code1:

    SELECT user_id, 
	MAX(time_stamp) AS last_stamp
    FROM Logins
WHERE LEFT(time_stamp, 4) = '2020'
GROUP BY user_id;

    newLogins

    Related Posts