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// JavaScript typically does not process SQL, but this represents the concept:
2letThis JavaScript code sample shows a SQL query embedded as a string, illustrating how you might define the query to find the latest login timestamp for 2020 logins.
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.