00:00:00

Description

Editorial

Latest 2020 Access Snapshot by User

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

logins =

[{"user_id":8,"time_stamp":"2020-02-01 05:10:53"},{"user_id":8,"time_stamp":"2020-12-30 00:46:50"},{"user_id":2,"time_stamp":"2020-01-16 02:49:50"},{"user_id":6,"time_stamp":"2020-06-30 15:06:07"},{"user_id":14,"time_stamp":"2021-01-06 11:59:59"},{"user_id":6,"time_stamp":"2021-04-21 14:06:06"},{"user_id":14,"time_stamp":"2019-07-14 09:00:00"},{"user_id":6,"time_stamp":"2019-03-07 00:18:15"},{"user_id":2,"time_stamp":"2019-08-25 07:59:08"}]

Loading problem...

101

00:00:00

Description

Editorial

Latest 2020 Access Snapshot by User

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

logins =

Latest 2020 Access Snapshot by User

Hints

Latest 2020 Access Snapshot by User

Hints