Loading problem...
An identity platform stores every successful account sign-in in a centralized audit table.
Table: logins
Data guarantee:
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:
Output requirements:
Supported submission environments:
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 |[
{"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.
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 |[
{"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.
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 |[]Explanation
No user has a login in 2020, so the result is empty.
Constraints