00:00:00

Description

Editorial

Rapid Repeat Confirmation Requesters

EASY10 pts

A user-security workflow sends one-time confirmation messages when users verify sensitive actions.

Table: Signups

user_id: unique user identifier
time_stamp: signup timestamp for the user

Table: Confirmations

user_id: user who requested a confirmation message
time_stamp: request timestamp
action: request outcome category ("confirmed" or "timeout")

Business rule: A user is considered an active re-requester if the same user generated at least two confirmation requests within an inclusive 24-hour interval. Two requests exactly 24 hours apart are valid. The action value does not affect eligibility.

Task: Return the IDs of all users who satisfy this rule.

Output requirements:

Return exactly one column named user_id.
Each user_id appears at most once.
Output order may be arbitrary.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Signups:
| user_id | time_stamp           |
|---------|----------------------|
| 3       | 2020-03-21 10:16:13  |
| 7       | 2020-01-04 13:57:59  |
| 2       | 2020-07-29 23:09:44  |
| 6       | 2020-12-09 10:39:37  |

Confirmations:
| user_id | time_stamp           | action    |
|---------|----------------------|-----------|
| 3       | 2021-01-06 03:30:46  | timeout   |
| 3       | 2021-01-06 03:37:45  | timeout   |
| 7       | 2021-06-12 11:57:29  | confirmed |
| 7       | 2021-06-13 11:57:30  | confirmed |
| 2       | 2021-01-22 00:00:00  | confirmed |
| 2       | 2021-01-23 00:00:00  | timeout   |
| 6       | 2021-10-23 14:14:14  | confirmed |
| 6       | 2021-10-24 14:14:13  | timeout   |

Output

[
  {"user_id":2},
  {"user_id":3},
  {"user_id":6}
]

Explanation

Users 2, 3, and 6 each have at least one request pair within 24 hours (inclusive). User 7's pair is 24 hours and 1 second apart, so it does not qualify.

Example

Input

Signups:
| user_id | time_stamp           |
|---------|----------------------|
| 10      | 2023-01-01 09:00:00  |

Confirmations:
| user_id | time_stamp           | action    |
|---------|----------------------|-----------|
| 10      | 2023-05-01 08:00:00  | confirmed |
| 10      | 2023-05-02 08:00:00  | timeout   |

Output

[
  {"user_id":10}
]

Explanation

The two requests are exactly 24 hours apart, which is included by the rule.

Example

Input

Signups:
| user_id | time_stamp           |
|---------|----------------------|
| 21      | 2022-02-10 12:00:00  |
| 22      | 2022-02-10 12:00:00  |

Confirmations:
| user_id | time_stamp           | action    |
|---------|----------------------|-----------|
| 21      | 2022-08-01 10:00:00  | confirmed |
| 21      | 2022-08-02 10:00:01  | timeout   |
| 22      | 2022-08-01 10:00:00  | timeout   |

Output

[]

Explanation

User 21 misses the window by one second, and user 22 has only one request.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in Signups <= 250000
0 <= number of rows in Confirmations <= 400000
Signups.user_id values are unique
(Confirmations.user_id, Confirmations.time_stamp) pairs are unique
Confirmations.user_id references Signups.user_id
action is one of: confirmed, timeout
time_stamp values are valid datetime values
A qualifying pair has time difference <= 24 * 60 * 60 seconds
Return exactly one column: user_id
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with one column user_id

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Signups =

[{"user_id":3,"time_stamp":"2020-03-21 10:16:13"},{"user_id":7,"time_stamp":"2020-01-04 13:57:59"},{"user_id":2,"time_stamp":"2020-07-29 23:09:44"},{"user_id":6,"time_stamp":"2020-12-09 10:39:37"}]

Confirmations =

[{"user_id":3,"time_stamp":"2021-01-06 03:30:46","action":"timeout"},{"user_id":3,"time_stamp":"2021-01-06 03:37:45","action":"timeout"},{"user_id":7,"time_stamp":"2021-06-12 11:57:29","action":"confirmed"},{"user_id":7,"time_stamp":"2021-06-13 11:57:30","action":"confirmed"},{"user_id":2,"time_stamp":"2021-01-22 00:00:00","action":"confirmed"},{"user_id":2,"time_stamp":"2021-01-23 00:00:00","action":"timeout"},{"user_id":6,"time_stamp":"2021-10-23 14:14:14","action":"confirmed"},{"user_id":6,"time_stamp":"2021-10-24 14:14:13","action":"timeout"}]

Loading problem...

Rapid Repeat Confirmation Requesters (Easy) — Practice with Code Visualizer | OneNoughtOne

101

00:00:00

Description

Editorial

Rapid Repeat Confirmation Requesters

EASY10 pts

A user-security workflow sends one-time confirmation messages when users verify sensitive actions.

Table: Signups

user_id: unique user identifier
time_stamp: signup timestamp for the user

Table: Confirmations

user_id: user who requested a confirmation message
time_stamp: request timestamp
action: request outcome category ("confirmed" or "timeout")

Task: Return the IDs of all users who satisfy this rule.

Output requirements:

Return exactly one column named user_id.
Each user_id appears at most once.
Output order may be arbitrary.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Signups:
| user_id | time_stamp           |
|---------|----------------------|
| 3       | 2020-03-21 10:16:13  |
| 7       | 2020-01-04 13:57:59  |
| 2       | 2020-07-29 23:09:44  |
| 6       | 2020-12-09 10:39:37  |

Confirmations:
| user_id | time_stamp           | action    |
|---------|----------------------|-----------|
| 3       | 2021-01-06 03:30:46  | timeout   |
| 3       | 2021-01-06 03:37:45  | timeout   |
| 7       | 2021-06-12 11:57:29  | confirmed |
| 7       | 2021-06-13 11:57:30  | confirmed |
| 2       | 2021-01-22 00:00:00  | confirmed |
| 2       | 2021-01-23 00:00:00  | timeout   |
| 6       | 2021-10-23 14:14:14  | confirmed |
| 6       | 2021-10-24 14:14:13  | timeout   |

Output

[
  {"user_id":2},
  {"user_id":3},
  {"user_id":6}
]

Explanation

Users 2, 3, and 6 each have at least one request pair within 24 hours (inclusive). User 7's pair is 24 hours and 1 second apart, so it does not qualify.

Example

Input

Signups:
| user_id | time_stamp           |
|---------|----------------------|
| 10      | 2023-01-01 09:00:00  |

Confirmations:
| user_id | time_stamp           | action    |
|---------|----------------------|-----------|
| 10      | 2023-05-01 08:00:00  | confirmed |
| 10      | 2023-05-02 08:00:00  | timeout   |

Output

[
  {"user_id":10}
]

Explanation

The two requests are exactly 24 hours apart, which is included by the rule.

Example

Input

Signups:
| user_id | time_stamp           |
|---------|----------------------|
| 21      | 2022-02-10 12:00:00  |
| 22      | 2022-02-10 12:00:00  |

Confirmations:
| user_id | time_stamp           | action    |
|---------|----------------------|-----------|
| 21      | 2022-08-01 10:00:00  | confirmed |
| 21      | 2022-08-02 10:00:01  | timeout   |
| 22      | 2022-08-01 10:00:00  | timeout   |

Output

[]

Explanation

User 21 misses the window by one second, and user 22 has only one request.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in Signups <= 250000
0 <= number of rows in Confirmations <= 400000
Signups.user_id values are unique
(Confirmations.user_id, Confirmations.time_stamp) pairs are unique
Confirmations.user_id references Signups.user_id
action is one of: confirmed, timeout
time_stamp values are valid datetime values
A qualifying pair has time difference <= 24 * 60 * 60 seconds
Return exactly one column: user_id
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with one column user_id

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Signups =

[{"user_id":3,"time_stamp":"2020-03-21 10:16:13"},{"user_id":7,"time_stamp":"2020-01-04 13:57:59"},{"user_id":2,"time_stamp":"2020-07-29 23:09:44"},{"user_id":6,"time_stamp":"2020-12-09 10:39:37"}]

Confirmations =

Rapid Repeat Confirmation Requesters

Hints

Rapid Repeat Confirmation Requesters

Hints