Back to questions
New TikTok users sign up with their emails and each user receives a text confirmation to activate their account. Assume you are given the below tables about emails and texts.
Write a query to display the ids of the users who did not confirm on the first day of sign-up, but confirmed on the second day.
Assumption:
Column Name | Type |
---|---|
email_id | integer |
user_id | integer |
signup_date | datetime |
email_id | user_id | signup_date |
---|---|---|
125 | 7771 | 06/14/2022 00:00:00 |
433 | 1052 | 07/09/2022 00:00:00 |
Column Name | Type |
---|---|
text_id | integer |
email_id | integer |
signup_action | string ('Confirmed', 'Not confirmed') |
action_date | datetime |
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 |
user_id |
---|
1052 |
User 1052 is the only user who confirmed their sign up on the second day.
The dataset you are querying against may have different input & output - this is just an example!
Credits to Deepak K @ Linkedin for sharing such an easy and concise solution that we have to share it with you.
There are 2 conditions that we have to fulfill:
First, we join the and tables on the matching field. Note that you can skip this step as we want to show you how condition no. 1 is defined.
Showing 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 |
Let's interpret the output together.
Now that you understand how we're fulfilling those conditions, let's incorporate them into the solution.
Condition #1: Users who confirmed on the second day
Note that you don't need to retrieve all the columns using .
The condition in the clause essentially means as we only want users who confirmed on the second day as reflected in the field, then we can simply take and add 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 |
Did you see that the is 1 day after the ? We have fulfilled the 1st condition. Moving on to the second condition.
Condition #2: 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 have the information on users who confirmed their accounts on the day after their sign-up.
Note that the question asks you to output user ids only, not the entire table.
Solution:
PostgreSQL 14