Second Day Verification - Problem

You are given two tables: emails and texts.

The emails table contains information about user signups with their email ID, user ID, and signup date.

The texts table contains verification actions for each email, including whether the signup was verified or not and the action date.

Task: Find the user IDs of those who verified their sign-up on the second day after their signup date.

Return the result ordered by user_id in ascending order.

Table Schema

emails
Column Name Type Description
email_id PK int Email identifier
user_id PK int User identifier
signup_date datetime Date and time when user signed up
Primary Key: (email_id, user_id)
texts
Column Name Type Description
text_id PK int Text message identifier
email_id PK int Email identifier (foreign key)
signup_action enum Verification status: 'Verified' or 'Not Verified'
action_date datetime Date and time of verification action
Primary Key: (text_id, email_id)

Input & Output

Example 1 — Basic Verification Check
Input Tables:
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
7771
💡 Note:

User 7771 (email 125) signed up on 2022-06-14 and verified on 2022-06-15 (next day). User 7005 (email 234) signed up on 2022-08-20 and verified on 2022-08-21 (next day). User 1052 was not verified, so excluded.
Example 2 — No Second Day Verifications
Input Tables:
emails
email_id user_id signup_date
100 1001 2022-05-01 10:00:00
texts
text_id email_id signup_action action_date
10 100 Verified 2022-05-03 12:00:00
Output:
user_id
💡 Note:

User 1001 verified on the third day (2022-05-03) instead of the second day, so they are not included in the result.

Constraints

  • 1 ≤ email_id, user_id, text_id ≤ 10000
  • signup_action is either 'Verified' or 'Not Verified'
  • signup_date and action_date are valid datetime values

Visualization

Tap to expand
Second Day Verification INPUT emails table email_id | user_id | signup_date 125 | 7771 | 06/14/2022 433 | 1052 | 07/09/2022 236 | 6230 | 06/28/2022 111 | 8832 | 08/01/2022 texts table text_id|email_id|action |date 6878 | 125 | Confirmed| 06/14 6997 | 433 | Confirmed| 07/10 7000 | 433 | Confirmed| 07/11 6800 | 236 | Confirmed| 06/29 6512 | 111 | NotClicked| 08/02 6650 | 111 | Confirmed| 08/03 Join on: emails.email_id = texts.email_id Filter: action = 'Confirmed' ALGORITHM STEPS 1 JOIN Tables Link emails with texts ON email_id = email_id 2 Filter Confirmed WHERE signup_action = 'Confirmed' 3 Calculate Date Diff action_date - signup_date DATEDIFF(day,...) = 1 4 Return user_id ORDER BY user_id ASC Date Difference Check: user 7771: 06/14 to 06/14 = 0 days X user 1052: 07/09 to 07/10 = 1 day OK user 6230: 06/28 to 06/29 = 1 day OK user 8832: 08/01 to 08/03 = 2 days X FINAL RESULT Verified Users (Day 2): user_id: 1052 user_id: 6230 Output Array: [1052, 6230] 2 Users Found Confirmed exactly 1 day after signup Key Insight: "Second day" means exactly 1 day after signup (DATEDIFF = 1), not 2 days. Use DATEDIFF(day, signup_date, action_date) = 1 to find users who verified on the day after they signed up. Remember to filter for 'Confirmed' action only and sort results by user_id ascending. TutorialsPoint - Second Day Verification | Optimal Solution

Related Problems

Asked in
Facebook 28 Amazon 22 Google 19
23.4K Views
Medium Frequency
~12 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen