Back to questions
Second Day Confirmation [TikTok SQL Interview Question]
Assume you're given tables with information about TikTok user sign-ups and confirmations through email and text. New users on TikTok sign up using their email addresses, and upon sign-up, each user receives a text message confirmation to activate their account.
Write a query to display the user IDs of those who did not confirm their sign-up on the first day, but confirmed on the second day.
Definition:
- refers to the date when users activated their accounts and confirmed their sign-up through text messages.
Table:
| Column Name | Type |
|---|---|
| email_id | integer |
| user_id | integer |
| signup_date | datetime |
Example Input:
| email_id | user_id | signup_date |
|---|---|---|
| 125 | 7771 | 06/14/2022 00:00:00 |
| 433 | 1052 | 07/09/2022 00:00:00 |
Table:
| Column Name | Type |
|---|---|
| text_id | integer |
| email_id | integer |
| signup_action | string ('Confirmed', 'Not confirmed') |
| action_date | datetime |
Example Input:
| text_id | email_id | signup_action | action_date |
|---|---|---|---|
| 6878 | 125 | Confirmed | 06/14/2022 00:00:00 |
| 6997 | 433 | Not Confirmed | 07/09/2022 00:00:00 |
| 7000 | 433 | Confirmed | 07/10/2022 00:00:00 |
Example Output:
| user_id |
|---|
| 1052 |
Explanation:
Only User 1052 confirmed their sign-up on the second day.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Credits to Deepak K @ Linkedin for sharing this easy and concise solution that fulfills two conditions:
- Users who confirmed on the second day.
- The texts received must say 'Confirmed'.
To begin, we join the and tables on the matching field. Feel free to skip this step if you wish as our intention is to clarify the definition of condition no. 1 for you.
Output with selected rows:
| email_id | user_id | signup_date | text_id | email_id | signup_action | action_date |
|---|---|---|---|---|---|---|
| 433 | 1052 | 07/09/2022 00:00:00 | 6997 | 433 | Not confirmed | 07/09/2022 00:00:00 |
| 433 | 1052 | 07/09/2022 00:00:00 | 7000 | 433 | Confirmed | 07/10/2022 00:00:00 |
| 236 | 6950 | 07/01/2022 00:00:00 | 9841 | 236 | Confirmed | 07/01/2022 00:00:00 |
| 450 | 8963 | 08/02/2022 00:00:00 | 6800 | 450 | Not confirmed | 08/03/2022 00:00:00 |
| 555 | 8963 | 08/09/2022 00:00:00 | 1255 | 555 | Not confirmed | 08/09/2022 00:00:00 |
| 555 | 8963 | 08/09/2022 00:00:00 | 2660 | 555 | Not confirmed | 08/10/2022 00:00:00 |
| 555 | 8963 | 08/09/2022 00:00:00 | 2800 | 555 | Confirmed | 08/11/2022 00:00:00 |
Next, we interpret the output together:
- Rows 1-2: User 1052 signed up on 07/09/2022 and confirmed their account on the next day, 07/10/2022. This satisfies both conditions.
- Row 3: User 6950 signed up and confirmed their account on the same day, 07/01/2022, so this user fails both conditions.
- Rows 4-7: User 8963 signed up twice, once on 08/02/2022 and another time on 08/09/2022, and only confirmed their account on 08/11/2022, which is 3 days after their signup. So, the first condition is not fulfilled.
Now that you understand how to fulfill these conditions, let's incorporate them into the solution.
Condition #1: Users who confirmed on the second day
The condition in the WHERE clause means we only want users who confirmed on the second day after their signup, as reflected in the field. We achieve this by taking and adding an interval of 1 day.
| email_id | user_id | signup_date | text_id | signup_action | action_date |
|---|---|---|---|---|---|
| 433 | 1052 | 07/09/2022 00:00:00 | 7000 | Confirmed | 07/10/2022 00:00:00 |
| 450 | 8963 | 08/02/2022 00:00:00 | 6800 | Not confirmed | 08/03/2022 00:00:00 |
| 555 | 8963 | 08/09/2022 00:00:00 | 2660 | Not confirmed | 08/10/2022 00:00:00 |
| 741 | 1235 | 07/25/2022 00:00:00 | 1568 | Confirmed | 07/26/2022 00:00:00 |
As you can see, the is 1 day after the , fulfilling the first condition. Now let's move on to the second condition.
Condition #2: The texts received must say 'Confirmed'
In addition to the first condition, we add the condition ' in the WHERE clause to ensure that the texts received must say 'Confirmed'.
| email_id | user_id | signup_date | text_id | signup_action | action_date |
|---|---|---|---|---|---|
| 433 | 1052 | 07/09/2022 00:00:00 | 7000 | Confirmed | 07/10/2022 00:00:00 |
| 741 | 1235 | 07/25/2022 00:00:00 | 1568 | Confirmed | 07/26/2022 00:00:00 |
Finally, we retrieve the unique user IDs only.
PostgreSQL 14