Day-Two Signup Verification Users

Easy

An identity onboarding team wants to measure who successfully confirms account ownership on the day after registration. You are given two tables: Emails email_id: email record identifier user_id: account identifier signup_date: timestamp when the account registration email was created Texts text_id: verification text event identifier email_id: email record identifier signup_action: either "Verified" or "Not Verified" action_date: timestamp when the verification action was logged Task: Find all users who have at least one verification text such that: signup_action = "Verified" The action happened on the calendar day immediately after signup_date (compare by date, not by exact elapsed hours) Output requirements: Return exactly one column in this order: user_id Return each qualifying user only once Sort by user_id ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Emails:
| email_id | user_id | signup_date          |
|----------|---------|----------------------|
| 125      | 7771    | 2022-06-14 09:30:00  |
| 433      | 1052    | 2022-07-09 08:15:00  |
| 234      | 7005    | 2022-08-20 10:00:00  |

Texts:
| text_id | email_id | signup_action | action_date          |
|---------|----------|---------------|----------------------|
| 1       | 125      | Verified      | 2022-06-15 08:30:00  |
| 2       | 433      | Not Verified  | 2022-07-10 10:45:00  |
| 4       | 234      | Verified      | 2022-08-21 09:30:00  |

Output

[
  {"user_id":7005},
  {"user_id":7771}
]

Explanation

Users 7005 and 7771 have a Verified action on date(signup_date + 1 day). User 1052 is excluded because the second-day action is Not Verified.

Example 2

Input

Emails:
| email_id | user_id | signup_date          |
|----------|---------|----------------------|
| 10       | 3001    | 2023-01-05 23:50:00  |
| 11       | 3002    | 2023-01-05 00:05:00  |

Texts:
| text_id | email_id | signup_action | action_date          |
|---------|----------|---------------|----------------------|
| 5       | 10       | Verified      | 2023-01-06 00:01:00  |
| 6       | 11       | Verified      | 2023-01-05 23:59:59  |

Output

[
  {"user_id":3001}
]

Explanation

Verification uses calendar-date logic. Email 10 qualifies on next date; email 11 does not because action date is still the signup date.

Example 3

Input

Emails:
| email_id | user_id | signup_date          |
|----------|---------|----------------------|
| 501      | 9001    | 2024-02-28 11:00:00  |
| 502      | 9001    | 2024-03-01 09:00:00  |
| 503      | 9002    | 2024-03-03 07:00:00  |

Texts:
| text_id | email_id | signup_action | action_date          |
|---------|----------|---------------|----------------------|
| 20      | 501      | Verified      | 2024-02-29 08:00:00  |
| 21      | 502      | Verified      | 2024-03-02 10:00:00  |
| 22      | 503      | Not Verified  | 2024-03-04 07:30:00  |
| 23      | 503      | Verified      | 2024-03-05 08:30:00  |

Output

[
  {"user_id":9001}
]

Explanation

User 9001 appears once even though two emails qualify. User 9002 does not qualify because the Verified action is two days after signup.

Constraints

1 <= number of rows in Emails <= 200000
1 <= number of rows in Texts <= 200000
(email_id, user_id) is unique in Emails
(text_id, email_id) is unique in Texts
signup_action is either 'Verified' or 'Not Verified'
signup_date and action_date are valid timestamps
Compare dates using calendar-day semantics
Output must contain exactly one column: user_id
Each qualifying user_id should appear once
Sort output by user_id ascending
SQL submissions are evaluated on SQLite 3.27.2
Pandas submissions must return a DataFrame with column [user_id]

Emails: | email_id | user_id | signup_date | |----------|---------|----------------------| | 125 | 7771 | 2022-06-14 09:30:00 | | 433 | 1052 | 2022-07-09 08:15:00 | | 234 | 7005 | 2022-08-20 10:00:00 | Texts: | text_id | email_id | signup_action | action_date | |---------|----------|---------------|----------------------| | 1 | 125 | Verified | 2022-06-15 08:30:00 | | 2 | 433 | Not Verified | 2022-07-10 10:45:00 | | 4 | 234 | Verified | 2022-08-21 09:30:00 |

Emails: | email_id | user_id | signup_date | |----------|---------|----------------------| | 10 | 3001 | 2023-01-05 23:50:00 | | 11 | 3002 | 2023-01-05 00:05:00 | Texts: | text_id | email_id | signup_action | action_date | |---------|----------|---------------|----------------------| | 5 | 10 | Verified | 2023-01-06 00:01:00 | | 6 | 11 | Verified | 2023-01-05 23:59:59 |

Emails: | email_id | user_id | signup_date | |----------|---------|----------------------| | 501 | 9001 | 2024-02-28 11:00:00 | | 502 | 9001 | 2024-03-01 09:00:00 | | 503 | 9002 | 2024-03-03 07:00:00 | Texts: | text_id | email_id | signup_action | action_date | |---------|----------|---------------|----------------------| | 20 | 501 | Verified | 2024-02-29 08:00:00 | | 21 | 502 | Verified | 2024-03-02 10:00:00 | | 22 | 503 | Not Verified | 2024-03-04 07:30:00 | | 23 | 503 | Verified | 2024-03-05 08:30:00 |