Rapid Repeat Confirmation Requesters

Easy

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 1

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 2

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 3

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.

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

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 |

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 |

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 |