00:00:00

Description

Editorial

Day-Two Signup Verification Users

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Texts =

[{"text_id":1,"email_id":125,"signup_action":"Verified","action_date":"2022-06-15 08:30:00"},{"text_id":2,"email_id":433,"signup_action":"Not Verified","action_date":"2022-07-10 10:45:00"},{"text_id":4,"email_id":234,"signup_action":"Verified","action_date":"2022-08-21 09:30:00"}]

Emails =

[{"email_id":125,"user_id":7771,"signup_date":"2022-06-14 09:30:00"},{"email_id":433,"user_id":1052,"signup_date":"2022-07-09 08:15:00"},{"email_id":234,"user_id":7005,"signup_date":"2022-08-20 10:00:00"}]

Loading problem...

101

00:00:00

Description

Editorial

Day-Two Signup Verification Users

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Texts =

Emails =

Day-Two Signup Verification Users

Hints

Day-Two Signup Verification Users

Hints