9/19 每日一題(尋找2020年間登入日期最靠近12月31日的時間戳記)

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. 

# Write your MySQL query statement below

SELECT user_id, MAX(time_stamp) as last_stamp

FROM Logins

WHERE YEAR(time_stamp) = '2020'

GROUP BY user_id

---------------------------------------

def latest_login(logins: pd.DataFrame) -> pd.DataFrame:

    #將time_stamp轉datetime類型

    logins['time_stamp']=pd.to_datetime(logins['time_stamp'])

    #選出2020年

    logins_2020=logins[logins['time_stamp'].between('2020-01-01','2020-12-31')]

    #找出靠近年底紀錄

    logins_2020['days_to_end_of_year']=(pd.to_datetime('2020-12-31')-logins_2020['time_stamp']).dt.days

    print(f'logins_2020=')

    print(logins_2020)

    close_year_end=logins_2020.loc[logins_2020.groupby('user_id')['days_to_end_of_year'].idxmin()]

    print(f'close_yrar_end=')

    print(close_year_end)

    #改欄位名稱

    close_year_end.rename(columns={'time_stamp':'last_stamp'},inplace=True)

    return close_year_end[['user_id','last_stamp']]

logins_2020=

   user_id          time_stamp  days_to_end_of_year

0        6 2020-06-30 15:06:07                  183

3        8 2020-02-01 05:10:53                  333

4        8 2020-12-30 00:46:50                    0

5        2 2020-01-16 02:49:50                  349

close_yrar_end=

   user_id          time_stamp  days_to_end_of_year

5        2 2020-01-16 02:49:50                  349

0        6 2020-06-30 15:06:07                  183

4        8 2020-12-30 00:46:50                    0