Back to questions

Second Day Confirmation TikTok SQL Interview Question

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 NameType
email_idinteger
user_idinteger
signup_datedatetime

Example Input:

email_iduser_idsignup_date
125777106/14/2022 00:00:00
433105207/09/2022 00:00:00

Table:

Column NameType
text_idinteger
email_idinteger
signup_actionstring ('Confirmed', 'Not confirmed')
action_datedatetime

Example Input:

text_idemail_idsignup_actionaction_date
6878125Confirmed06/14/2022 00:00:00
6997433Not Confirmed07/09/2022 00:00:00
7000433Confirmed07/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!

Input

(PostgreSQL)

Output