Sponsored
Sponsored
In this approach, we use SQL queries to filter logins by the year 2020 and then use an aggregation function to find the latest login timestamp for each user. The process involves:
The SQL approach should have a time complexity of O(n) for scanning the table, where n is the number of rows, and a space complexity of O(u), where u is the number of unique users who logged in during 2020.
1# Python has no direct query execution like SQL, but we can conceptualize it:
2query = This code defines a SQL query that filters logins to those occurring in 2020 using WHERE condition with YEAR extraction and then finds the latest timestamp per user using GROUP BY and MAX() functions.
This approach involves loading the data and performing the filtering and aggregation in-memory using the language's data processing capabilities. We will iterate over the logins, filter out non-2020 entries, and use a data structure to track the latest login for each user.
The time complexity is O(n), with n as the number of logins, due to the single linear pass through the logins. Space complexity is O(u) based on the number of unique users who logged in during 2020.
1from datetime import datetime
2
3logins = [
4 {'user_id': 6, 'time_stamp': '2020-06-30 15:06:07'},
5 {'user_id': 6, 'time_stamp': '2021-04-21 14:06:06'},
6 {'user_id': 6, 'time_stamp': '2019-03-07 00:18:15'},
7 {'user_id': 8, 'time_stamp': '2020-02-01 05:10:53'},
8 {'user_id': 8, 'time_stamp': '2020-12-30 00:46:50'},
9 {'user_id': 2, 'time_stamp': '2020-01-16 02:49:50'},
10 {'user_id': 2, 'time_stamp': '2019-08-25 07:59:08'},
11 {'user_id': 14, 'time_stamp': '2019-07-14 09:00:00'},
12 {'user_id': 14, 'time_stamp': '2021-01-06 11:59:59'}
13]
14
15latest_logins = {}
16
17for login in logins:
18 ts = datetime.strptime(login['time_stamp'], '%Y-%m-%d %H:%M:%S')
19 if ts.year == 2020:
20 if login['user_id'] not in latest_logins or latest_logins[login['user_id']] < ts:
21 latest_logins[login['user_id']] = ts
22
23result = [{'user_id': user, 'last_stamp': latest_logins[user]} for user in latest_logins]This Python code iterates through the list of logins, filters out those from the year 2020, and updates a dictionary to track the latest timestamp for each user. The result is then constructed as a list of dictionaries for each user's latest login timestamp.