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 Problem OverviewInput: Two Tablesemailsemail_iduser_idsignup125777106-14textsemail_idactiondate125Verified06-15JOINFilter: Date Diff = 1Processing1. JOIN tables2. Filter: Verified3. Date check: +1 dayResultOutputuser_id70057771
Understanding the Visualization
1
Input Tables
Two related tables with signup and verification data
2
Join & Filter
JOIN on email_id and filter by date difference
3
Result
User IDs who verified on second day
Key Takeaway
🎯 Key Insight: Use date arithmetic with JOINs to find time-based relationships between tables

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