logo

Back to questions

Signup Activation Rate [TikTok SQL Interview Question]

Medium

New TikTok users sign up with their emails. They confirmed their signup by replying to the text confirmation to activate their accounts. Users may receive multiple text messages for account confirmation until they have confirmed their new account.

Write a query to find the activation rate of the users. Round the percentage to 2 decimal places.

Definitions:

  • table contain the information of user signup details.
  • table contains the users' activation information.

As of 29 Nov 2022, the term confirmation rate is changed to activation rate to reflect the nature of the new user activation process.

Table:

Column NameType
email_idinteger
user_idinteger
signup_datedatetime

Example Input:

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

Table:

Column NameType
text_idinteger
email_idinteger
signup_actionvarchar

Example Input:

text_idemail_idsignup_action
6878125Confirmed
6920236Not Confirmed
6994236Confirmed

'Confirmed' in means the user has activated their account and successfully completed the signup process.

Example Output:

confirm_rate
0.67

Explanation:

67% of users have successfully completed their signup and activated their accounts. The remaining 33% have not yet replied to the text to confirm their signup.

The dataset you are querying against may have different input & output - this is just an example!

PostgreSQL 14