Signup Confirmation Rate — TikTok SQL Questions

Solving SQL Question asked by TikTok

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