LeetCode 3172 - Second Day Verification

LeetCode Problem 3172

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

The problem requires identifying users who verified their signup exactly on the second day after registering. We have two tables: emails and texts. The emails table contains the email_id, the user_id, and the signup_date. The texts table records actions taken by emails, including whether they were verified, along with the action_date.

The task is to find all user_ids where the corresponding email had a Verified action exactly one day after the signup_date. Note that "second day" means the day immediately after the signup date, ignoring the exact time of day. For example, signing up at 2022-06-14 23:00:00 and verifying at 2022-06-15 01:00:00 counts as the second day.

The output should return a single column user_id sorted in ascending order.

Important considerations include: some emails may never have a Verified action, some may have multiple actions, and the texts table may include Not Verified actions which must be ignored.

Edge cases involve time zones or partial day differences, but here we assume standard datetime comparison by date only. The problem guarantees valid data and non-null datetime values.

Approaches

A brute-force approach would involve joining the emails and texts tables on email_id, filtering for rows with signup_action = 'Verified', and then computing the exact day difference between action_date and signup_date. While this is correct, it may be unnecessarily heavy if there are millions of records since it calculates date differences for every row before filtering.

The optimal approach leverages SQL's date arithmetic functions to compute the day difference directly in the WHERE clause. By filtering for DATEDIFF(action_date, signup_date) = 1 and signup_action = 'Verified', we can directly retrieve only the relevant rows. Sorting by user_id ensures the final output is in the required order.

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(n * m)	O(n * m)	Join tables, compute day differences for all rows, filter afterwards
Optimal	O(n + m)	O(n + m)	Use SQL date difference in WHERE clause to filter relevant rows directly

Algorithm Walkthrough

1. Perform an inner join between the emails and texts tables on the email_id column. This combines each email with its corresponding actions.
2. Filter the joined rows to retain only those where signup_action is 'Verified'. Actions that were 'Not Verified' are irrelevant.
3. Use SQL's DATEDIFF function to compute the difference in days between action_date and signup_date.
4. Retain only those rows where the day difference is exactly 1, which corresponds to the second day verification.
5. Select the user_id from the filtered rows.
6. Apply DISTINCT to remove duplicates in case an email has multiple verified actions on the second day.
7. Order the result by user_id in ascending order for the final output.

Why it works: The algorithm works because it directly filters the data using both the verification status and the precise day difference, ensuring that only users who verified exactly on the second day are returned. The ordering and deduplication ensure correctness and alignment with the problem requirements.

Python Solution

class Solution:
    def secondDayVerification(self) -> str:
        return """
        SELECT DISTINCT e.user_id
        FROM emails e
        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 ASC;
        """

The Python implementation is a simple wrapper around the SQL query. The SQL logic performs the inner join between emails and texts, filters for verified actions exactly one day after signup, eliminates duplicates using DISTINCT, and orders the result by user_id.

Go Solution

func SecondDayVerification() string {
    return `
    SELECT DISTINCT e.user_id
    FROM emails e
    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 ASC;
    `
}

The Go version is equivalent to the Python version. Go functions return the SQL string, which can then be executed using a database driver. The logic inside the SQL remains unchanged. There is no difference in handling edge cases between Python and Go here because the filtering is entirely done by SQL.

Worked Examples

Consider the first row in the input tables:

• Email 125 with user_id 7771 signed up at 2022-06-14 09:30:00.
• Text action 1 for email_id 125 is Verified at 2022-06-15 08:30:00.
• The day difference is 1 (second day).
• User 7771 is included in the output.

Similarly, email 234 with user_id 7005 verified on the second day. Email 433 verified Not Verified on the second day, which is ignored.

After sorting, the output table is:

+---------+
| user_id |
+---------+
| 7005    |
| 7771    |
+---------+

Complexity Analysis

Measure	Complexity	Explanation
Time	O(n + m)	The join operation is linear with respect to the number of rows in each table, and filtering by day difference is constant per row
Space	O(n + m)	Storing intermediate join results requires space proportional to input sizes

The SQL query uses built-in date functions and indexing on primary keys to efficiently compute the result without scanning unnecessary rows.

Test Cases

# Provided example
assert Solution().secondDayVerification() == """
SELECT DISTINCT e.user_id
FROM emails e
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 ASC;
"""  # standard case

# Edge case: multiple verified actions on the same second day
# Edge case: No verified actions
# Edge case: Verified action on the same day (should not appear)

Test	Why
Provided example	Validates basic second day verification logic
Multiple verified actions	Ensures DISTINCT removes duplicates
No verified actions	Returns empty result correctly
Verified same day	Ensures day difference filtering works correctly

Edge Cases

1. Multiple Verifications on the Second Day: An email may have multiple Verified actions on the second day. Using DISTINCT ensures the user_id appears only once in the output.
2. No Verification Action: Some users may sign up but never verify. Our query filters signup_action = 'Verified', so these users are correctly excluded from the result.
3. Verification on Signup Day or Later than Second Day: Users verifying on the same day (DATEDIFF = 0) or after the second day (DATEDIFF > 1) must be excluded. Filtering with DATEDIFF = 1 ensures these cases are handled correctly.

This solution correctly handles all standard and edge cases efficiently, relying on SQL's date arithmetic and filtering capabilities.