Second Day Verification - Practice Coding Problems

Second Day Verification - Problem

Database Easy

Second Day Verification is a SQL database problem that challenges you to identify users who verified their email addresses exactly one day after signing up.

You're given two tables: emails containing user signup information and texts containing verification actions. Your task is to find users who successfully verified their accounts on the second day (exactly 1 day after their signup date).

Goal: Return user IDs of those who verified on the second day, ordered by user_id ascending.

Key Insight: You need to join the tables, filter for 'Verified' actions, and check if the action date is exactly 1 day after the signup date using date arithmetic.

Input & Output

example_1.sql — Basic Case

$ Input: emails: [(125, 7771, '2022-06-14 09:30:00'), (433, 1052, '2022-07-09 08:15:00'), (234, 7005, '2022-08-20 10:00:00')]\ntexts: [(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: [7005, 7771]

💡 Note: User 7771 signed up on 2022-06-14 and verified on 2022-06-15 (next day). User 7005 signed up on 2022-08-20 and verified on 2022-08-21 (next day). User 1052 did not verify successfully.

example_2.sql — Same Day Verification

$ Input: emails: [(100, 5001, '2022-01-01 10:00:00'), (200, 5002, '2022-01-02 11:00:00')]\ntexts: [(10, 100, 'Verified', '2022-01-01 15:00:00'), (20, 200, 'Verified', '2022-01-04 12:00:00')]

› Output: []

💡 Note: User 5001 verified on the same day (not second day). User 5002 verified on the third day (not second day). No users verified on exactly the second day.

example_3.sql — Multiple Actions

$ Input: emails: [(300, 6001, '2022-03-01 08:00:00')]\ntexts: [(30, 300, 'Not Verified', '2022-03-02 09:00:00'), (31, 300, 'Verified', '2022-03-02 14:00:00')]

› Output: [6001]

💡 Note: User 6001 had multiple verification attempts on the second day. Since they eventually verified on the second day (2022-03-02), they should be included in results.

Constraints

1 ≤ emails.length ≤ 1000
1 ≤ texts.length ≤ 1000
email_id and user_id are unique in emails table
signup_action is either 'Verified' or 'Not Verified'
All dates are in valid datetime format
Each email_id in texts table exists in emails table

Visualization

Tap to expand

Understanding the Visualization

User Signs Up

User creates account and receives verification email

Second Day Check

We look for verification actions exactly 1 day later

Filter Verified

Only count successful 'Verified' actions

Collect Results

Return user IDs who verified on day 2

Key Takeaway

🎯 Key Insight: Use INNER JOIN with date arithmetic (DATEDIFF) to efficiently find users who verified exactly 1 day after signup in a single query

Asked in

f Facebook 35 G Google 28 a Amazon 22 ⊞ Microsoft 18

The optimal solution uses an INNER JOIN between the emails and texts tables, filtering for 'Verified' actions where the verification date is exactly 1 day after the signup date using DATEDIFF function. This approach is efficient with O(m + n) time complexity.

Common Approaches

Approach	Time	Space	Notes
✓ JOIN with Date Arithmetic (Optimal)	O(m + n)	O(k)	Use INNER JOIN with date functions to find second-day verifications in one pass
Nested Query Approach	O(m × n)	O(1)	Use subqueries to manually check each user's verification timing

JOIN with Date Arithmetic (Optimal) — Algorithm Steps

INNER JOIN emails and texts tables on email_id
Filter for 'Verified' signup_action
Use DATE functions to check if action_date equals signup_date + 1 day
SELECT DISTINCT user_id to avoid duplicates
ORDER BY user_id ascending

Visualization

Tap to expand

Step-by-Step Walkthrough

JOIN Tables

Combine emails and texts on email_id

Filter Verified

Keep only 'Verified' actions

Date Math

Check if action_date = signup_date + 1 day

Return Results

Get distinct user_ids ordered

Code -

solution.c — C

// Optimal SQL Solution using JOIN
#include <stdio.h>
#include <string.h>

char* solution() {
    static char query[] = 
        "SELECT DISTINCT e.user_id "
        "FROM emails e "
        "INNER JOIN texts t ON e.email_id = t.email_id "
        "WHERE t.signup_action = 'Verified' "
        "AND DATE(t.action_date) = DATE(DATE_ADD(e.signup_date, INTERVAL 1 DAY)) "
        "ORDER BY e.user_id;";
    return query;
}

char* solutionAlternative() {
    static char query2[] = 
        "SELECT DISTINCT e.user_id "
        "FROM emails e "
        "INNER JOIN texts t ON e.email_id = t.email_id "
        "WHERE t.signup_action = 'Verified' "
        "AND DATEDIFF(t.action_date, e.signup_date) = 1 "
        "ORDER BY e.user_id;";
    return query2;
}

Time & Space Complexity

Time Complexity

⏱️

O(m + n)

Linear scan through both tables with JOIN optimization

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of matching records

✓ Linear Space

25.4K Views

Medium Frequency

~12 min Avg. Time

845 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

JOIN with Date Arithmetic (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler