Problem

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.

Examples

Example 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

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

### Method 1 - SQL (MySQL, PostgreSQL, Oracle)

We filter logins to only those in 2020, then group by user and select the latest timestamp for each user.

#### Code



    

         
         
         
        
            
            
        
            
            
                 
                 
                
            
        
            
            
        
            
            
                 
                 
                
            
        
            
            
        
            
            
                 
                 
                
            
        
            
            
        
    


    

        
         
        
        
         
         
        

             
            
             
            
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
            
            
            




1
2
3
4




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





        

        
        
         
        
        
         
         
        

             
            
             
            
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
            
            
            




1
2
3
4




SELECT user_id, MAX(time_stamp) AS last_stamp
FROM Logins
WHERE EXTRACT(YEAR FROM time_stamp) = 2020
GROUP BY user_id;





        

        
        
         
        
        
         
         
        

             
            
             
            
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
            
            
            




1
2
3
4




SELECT user_id, MAX(time_stamp) AS last_stamp
FROM Logins
WHERE EXTRACT(YEAR FROM time_stamp) = 2020
GROUP BY user_id;





        

        
        
         
        
        
    





#### Explanation

- We filter logins to only those in 2020 using the appropriate date function for each SQL dialect.
- We group by user and select the latest timestamp for each user.
- Only users who logged in during 2020 are included.

#### Complexity

- ⏰ Time complexity: `O(N)` where N is the number of logins.
- 🧺 Space complexity: `O(U)` where U is the number of unique users logging in 2020.