In this problem, we’re given two tables containing information about TikTok user sign-ups and their confirmation statuses. Users sign up using their email addresses, and upon signing up, they receive a text message to confirm their account. Our task is to identify users who did not confirm their sign-up on the first day but did confirm it on the second day. Let’s break down the requirements and construct an SQL query to solve this problem.

Problem Statement

We have two tables:

1. emails: Contains information about users and their sign-up dates.
2. texts: Contains information about text message confirmations sent to users.

Table: emails

Table: texts

Example Input:

emails Table:

texts Table:

Example Output:

Explanation:

• User 1052 did not confirm on the day they signed up (07/09/2022) but confirmed on the second day (07/10/2022).

Step-by-Step Solution

1. Identify First and Second-Day Confirmation Attempts: We’ll join the emails table with the texts table and identify the attempts to confirm on the first day (signup date) and the second day (the day after the signup date).
2. Filter Users: Filter for users who did not confirm on the first day but confirmed on the second day.
3. Select Unique User IDs: Extract and display the user IDs that meet the criteria.

SQL Query

Here’s the SQL query that solves this problem:

SELECT DISTINCT e.user_id
FROM emails e
JOIN texts t1 ON e.email_id = t1.email_id
JOIN texts t2 ON e.email_id = t2.email_id
WHERE t1.signup_action = 'Not Confirmed'
AND DATE(t1.action_date) = DATE(e.signup_date)
AND t2.signup_action = 'Confirmed'
AND DATE(t2.action_date) = DATE(e.signup_date + INTERVAL 1 DAY);

Explanation of the Query

1. Joining Tables (JOIN):

• We perform a self-join on the texts table to check both the first-day and second-day confirmation attempts. This is done by joining emails with two aliases of texts (t1 and t2).

1. First-Day Non-Confirmation (t1):

• We filter with t1.signup_action = 'Not Confirmed' to identify users who did not confirm their account on the sign-up date (DATE(t1.action_date) = DATE(e.signup_date)).

1. Second-Day Confirmation (t2):

• We ensure that t2.signup_action = 'Confirmed' captures the confirmation attempt on the second day (DATE(t2.action_date) = DATE(e.signup_date + INTERVAL 1 DAY)).

1. Select and Display (SELECT DISTINCT):

• Use DISTINCT to ensure each user ID is listed only once.

Conclusion

This query helps identify users who took a bit longer to confirm their accounts, specifically those who confirmed on the second day after their sign-up. By utilizing SQL joins and date filtering, we can extract meaningful insights from user activity data, an essential skill in data analysis and reporting.