logo

Back to questions

Signup Confirmation Rate

Medium

New TikTok users sign up with their emails, so each signup requires a text confirmation to activate the new user's account.

Write a SQL query to find the confirmation rate of people who confirmed their signups with text messages. Round the result to 2 decimal places.

Assumptions:

  • A user may fail to confirm many times with text. Once signup is confirmed for a user, they will not be able to initiate the same process again.
  • A user may not initiate the signup confirmation process at at all.

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

Example Output:

confirm_rate
0.67

Explanation

This output indicates that 67% of users have successfully confirmed their signup. The other 33% have either not initiated signup, or their signup is not confirmed yet.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.