logo

Back to questions

Signup Activation Rate TikTok SQL Interview Question

Signup Activation Rate

TikTok SQL Interview Question

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.

A senior analyst is interested to know the activation rate of specified users in the table. Write a query to find the activation rate. Round the percentage to 2 decimal places.

Definitions:

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

Assumptions:

  • The analyst is interested in the activation rate of specific users in the table, which may not include all users that could potentially be found in the table.
  • For example, user 123 in the table may not be in the table and vice versa.

Effective April 4th 2023, we added an assumption to the question to provide additional clarity.

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