Latest 2020 Access Snapshot by User

Easy

An identity platform stores every successful account sign-in in a centralized audit table. Table: logins user_id: unique account identifier time_stamp: login event timestamp with second-level precision Data guarantee: (user_id, time_stamp) is unique. Task: Build a year-specific snapshot for 2020. For every user who logged in at least once during calendar year 2020, return that user's most recent 2020 login timestamp. Business rules: Only timestamps inside 2020 are considered for selection. Users with no 2020 login must be excluded. A user's logins in 2019 or 2021 do not directly appear in output, but can coexist in input. Output requirements: Return exactly these columns in this order: user_id last_stamp Sort by user_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

logins:
| 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":2,"last_stamp":"2020-01-16 02:49:50"},
  {"user_id":6,"last_stamp":"2020-06-30 15:06:07"},
  {"user_id":8,"last_stamp":"2020-12-30 00:46:50"}
]

Explanation

Users 2, 6, and 8 have at least one 2020 login; for each, we keep the latest timestamp inside 2020. User 14 has no 2020 login and is excluded.

Example 2

Input

logins:
| user_id | time_stamp           |
|---------|----------------------|
| 101     | 2020-01-01 00:00:00  |
| 101     | 2020-12-31 23:59:59  |
| 101     | 2021-01-01 00:00:00  |
| 202     | 2019-12-31 23:59:59  |
| 202     | 2020-03-14 09:26:53  |
| 303     | 2021-05-01 10:00:00  |

Output

[
  {"user_id":101,"last_stamp":"2020-12-31 23:59:59"},
  {"user_id":202,"last_stamp":"2020-03-14 09:26:53"}
]

Explanation

Boundary timestamps at the start and end of 2020 are valid. Rows in 2021 are ignored for this year-specific snapshot.

Example 3

Input

logins:
| user_id | time_stamp           |
|---------|----------------------|
| 9001    | 2019-11-30 13:00:00  |
| 9001    | 2021-01-01 09:00:00  |
| 9002    | 2018-04-12 07:15:20  |
| 9003    | 2022-08-22 22:10:00  |

Output

[]

Explanation

No user has a login in 2020, so the result is empty.

Constraints

1 <= number of rows in logins <= 300000
1 <= user_id <= 1000000000
(user_id, time_stamp) is unique
time_stamp is a valid datetime value in YYYY-MM-DD HH:MM:SS format
Return exactly columns: user_id, last_stamp
Sort output by user_id ascending
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with required output columns

logins: | 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 |

logins: | user_id | time_stamp | |---------|----------------------| | 101 | 2020-01-01 00:00:00 | | 101 | 2020-12-31 23:59:59 | | 101 | 2021-01-01 00:00:00 | | 202 | 2019-12-31 23:59:59 | | 202 | 2020-03-14 09:26:53 | | 303 | 2021-05-01 10:00:00 |

logins: | user_id | time_stamp | |---------|----------------------| | 9001 | 2019-11-30 13:00:00 | | 9001 | 2021-01-01 09:00:00 | | 9002 | 2018-04-12 07:15:20 | | 9003 | 2022-08-22 22:10:00 |