Problem

Table: emails

1
2
3
4
5
6
7
8
9

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| email_id    | int      |
| user_id     | int      |
| signup_date | datetime |
+-------------+----------+
(email_id, user_id) is the primary key (combination of columns with unique values) for this table.
Each row of this table contains the email ID, user ID, and signup date.

Table: texts

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| text_id       | int      |
| email_id      | int      |
| signup_action | enum     |
| action_date   | datetime |
+---------------+----------+
(text_id, email_id) is the primary key (combination of columns with unique values) for this table.
signup_action is an enum type of ('Verified', 'Not Verified').
Each row of this table contains the text ID, email ID, signup action, and action date.

Write a Solution to find the user IDs of those who verified their sign-up on the second day.

Return the result table ordered by user_id in ascending order.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

Input:
emails table:
+----------+---------+---------------------+
| 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 table:
+---------+----------+--------------+---------------------+
| 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    |
+---------+
Explanation:
* User with user_id 7005 and email_id 234 signed up on 2022-08-20 10:00:00 and verified on second day of the signup.
* User with user_id 7771 and email_id 125 signed up on 2022-06-14 09:30:00 and verified on second day of the signup.

Solution

Method 1 - Join and Date Arithmetic

Intuition

We need to find users who verified their sign-up exactly on the second day after signup. This means the verification date is exactly one day after the signup date (ignoring the time part).

Approach

  1. Join emails and texts on email_id.
  2. Filter for signup_action = 'Verified'.
  3. Check if the date part of action_date is exactly one day after the date part of signup_date.
  4. Return user_id ordered ascending.

Code

1
2
3
4
5
6

SELECT DISTINCT e.user_id
FROM emails e
JOIN texts t ON e.email_id = t.email_id
WHERE t.signup_action = 'Verified'
  AND DATE(t.action_date) = DATE(e.signup_date) + INTERVAL 1 DAY
ORDER BY e.user_id ASC;
1
2
3
4
5
6

SELECT DISTINCT e.user_id
FROM emails e
JOIN texts t ON e.email_id = t.email_id
WHERE t.signup_action = 'Verified'
  AND t.action_date::date = e.signup_date::date + INTERVAL '1 day'
ORDER BY e.user_id ASC;
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# emails and texts are pandas DataFrames
import pandas as pd
def second_day_verification(emails, texts):
    df = pd.merge(emails, texts, on='email_id')
    df = df[df['signup_action'] == 'Verified']
    df['signup_date'] = pd.to_datetime(df['signup_date']).dt.date
    df['action_date'] = pd.to_datetime(df['action_date']).dt.date
    mask = (df['action_date'] - df['signup_date']).dt.days == 1
    result = df.loc[mask, 'user_id'].drop_duplicates().sort_values().reset_index(drop=True)
    return result
# result = second_day_verification(emails, texts)

Complexity

  • ⏰ Time complexity: O(N) (N = number of rows in emails/texts)
  • 🧺 Space complexity: O(N)