Difficulty: Easy | Acceptance: 77.00% | Paid: No Topics: Database
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 a user with ID user_id.
Write an SQL query to report the latest login for each user in the year 2020. Do not return the user who has not logged in 2020.
The result can be returned in any order.
The query result format is in the following example.
- Examples
- Constraints
- Approach 1: Using MAX and GROUP BY
- Approach 2: Using Window Functions
- Approach 3: Using Subquery
Examples
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 01:19:47 | | 8 | 2020-12-30 15:04:50 | | 2 | 2020-01-16 02:08:24 | | 2 | 2020-12-09 11:52:24 | | 14 | 2019-07-14 23:59:59 | | 14 | 2020-07-14 23:59:59 | +---------+---------------------+
Output: +---------+---------------------+ | user_id | last_stamp | +---------+---------------------+ | 6 | 2020-06-30 15:06:07 | | 8 | 2020-12-30 15:04:50 | | 2 | 2020-12-09 11:52:24 | | 14 | 2020-07-14 23:59:59 | +---------+---------------------+
Explanation:
- User 6 logged in at 2020-06-30 15:06:07, which is the only login in 2020.
- User 8 logged in at 2020-02-01 01:19:47 and 2020-12-30 15:04:50. The latest login is at 2020-12-30 15:04:50.
- User 2 logged in at 2020-01-16 02:08:24 and 2020-12-09 11:52:24. The latest login is at 2020-12-09 11:52:24.
- User 14 logged in at 2020-07-14 23:59:59, which is the only login in 2020.
Constraints
1 <= user_id <= 1000
Approach 1: Using MAX and GROUP BY
Intuition Group the logins by user and find the maximum timestamp for each user in 2020 using aggregation.
Steps
- Filter logins for the year 2020 using WHERE clause
- Group by user_id using GROUP BY
- Find the maximum time_stamp for each group using MAX function
import pandas as pd
def latest_logins(logins: pd.DataFrame) -> pd.DataFrame:
logins['year'] = pd.to_datetime(logins['time_stamp']).dt.year
result = logins[logins['year'] == 2020].groupby('user_id')['time_stamp'].max().reset_index()
result = result.rename(columns={'time_stamp': 'last_stamp'})
return result[['user_id', 'last_stamp']]Complexity
- Time: O(n) where n is the number of login records
- Space: O(k) where k is the number of unique users who logged in 2020
- Notes: Simple and efficient approach using aggregation
Approach 2: Using Window Functions
Intuition Use window functions to rank logins by timestamp for each user and select the latest one.
Steps
- Filter logins for the year 2020
- Use ROW_NUMBER() to rank logins by timestamp in descending order for each user
- Select only the rows with rank 1 (latest login)
import pandas as pd
def latest_logins(logins: pd.DataFrame) -> pd.DataFrame:
logins['year'] = pd.to_datetime(logins['time_stamp']).dt.year
logins_2020 = logins[logins['year'] == 2020].copy()
logins_2020['rank'] = logins_2020.groupby('user_id')['time_stamp'].rank(method='first', ascending=False)
result = logins_2020[logins_2020['rank'] == 1][['user_id', 'time_stamp']]
result = result.rename(columns={'time_stamp': 'last_stamp'})
return result.reset_index(drop=True)Complexity
- Time: O(n log n) due to sorting within each partition
- Space: O(n) for storing ranked results
- Notes: Window functions provide flexibility for more complex ranking requirements
Approach 3: Using Subquery
Intuition Find the latest login for each user by comparing each login with the maximum timestamp for that user in 2020.
Steps
- Filter logins for the year 2020
- For each login, check if its timestamp equals the maximum timestamp for that user
- Return only the matching records
import pandas as pd
def latest_logins(logins: pd.DataFrame) -> pd.DataFrame:
logins['year'] = pd.to_datetime(logins['time_stamp']).dt.year
logins_2020 = logins[logins['year'] == 2020].copy()
max_timestamps = logins_2020.groupby('user_id')['time_stamp'].max().reset_index()
max_timestamps.columns = ['user_id', 'max_stamp']
result = pd.merge(logins_2020, max_timestamps, on='user_id')
result = result[result['time_stamp'] == result['max_stamp']][['user_id', 'time_stamp']]
result = result.rename(columns={'time_stamp': 'last_stamp'})
return result.reset_index(drop=True)Complexity
- Time: O(n) for two passes through the data
- Space: O(k) where k is the number of unique users who logged in 2020
- Notes: More verbose but demonstrates the subquery pattern useful for complex conditions