- 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.
- 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
- 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
- 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.
- 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.
- Our SQL query gets accepted by platform.
- 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.