Blog

Signup Confirmation Rate — TikTok SQL Questions

  • Post last modified:December 15, 2022
  • Reading time:3 mins read

Introduction

  • In this article we will solve SignUp confirmation rate question which is asked by TikTok as per DataLemur website.

If you don’t know DatLemur then please do visit , it’s one of the best website to practice SQL questions and improve your SQL skills.

Question

  • As per the question, we have been given emails table and texts table.
  • Email table mainly provides info about user signup details and texts table provides text confirmation about the email_id of the user.

Here is the table info 

emails

texts

  • Our goal is to write SQL query that returns the confirmation rate of the users.
  • Confirmation rate is defined as number of users who confirmed their email / number of users who signup for the service.
  • Here is the output

Solution

  • We need to first count all the distinct email_id’s in the emails table , then we need to count how many confirmation action type in texts table.
  • If we somehow can get these two aggregation, then we can just divide them to get confirmation rate as output.
  • But at first lets left join texts table to emails table
select e.email_id, e.user_id, e.signup_date, t.signup_action
from emails e 
LEFT JOIN texts t 
on e.email_id = t.email_id
  • If we execute this sql , we will get below table.
  • We can see due to left join we have email_id for which either sign_up action doesn’t exist or it is Not Confirmed yet.
  • We can also see there are some duplicates records as well like one for email_id 236 , we have two action type , Confirmed and Not Confirmed. In this case user first didn’t confirm then there was again confirmation link sent and then he confirmed . so we have to consider the latest status in that case.
  • We can treat above table as base table or CTE , then use it get two important aggregation that we discuss earlier i.e distinct email_id and # of signup_action = ‘Confirmed’
select round(((select count(*) from cte where signup_action = 'Confirmed')*1.00) /
(select count(DISTINCT email_id) from cte),2) as confirm_rate
  • We are multiplying confirmation value to 1.00 so that the division becomes decimal instead of integer division. We are rounding up the confirm_rate value two 2 digits since its requirement.

Finalize Solution

  • Below is the finalize solution for the problem. We created CTE or base table first by performing left join.
  • Then we counted distinct emails and confirmation and then finally achieved confirm_rate value as asked .
with cte as (
select e.email_id, e.user_id, e.signup_date, t.signup_action
from emails e 
LEFT JOIN texts t 
on e.email_id = t.email_id
)

select round(((select count(*) from cte where signup_action = 'Confirmed')*1.00) /
(select count(DISTINCT email_id) from cte),2) as confirm_rate
  • Here is the final output of the problem.

Submission

  • Our SQL query gets accepted by platform. 

Conclusion

  • In this SQL problem , we used CTE i.e common table expression and subqueries to solve the signup rate metrics.
  • We also used Left Join to join two tables to get the base table ready in desired form.

Bonus Tip

Tags: , , , ,

Leave a Reply