Blog

Introduction

• In this article we will solve Apply Clickthrough Rate SQL question which is asked by Facebook 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

• We have been given event table , this table consist of app_id , events along with timestamp.
• All we have to do is to calculate CTR for each app id for year 2022

• Click through rate is defined as how many time app_id was clicked when it was shown to user. For example if we app_id was shown 4 time and user clicked it 2 time then the CTR % would be 50%

• For given input table we can see app_id 123 was shown 2 times and and user clicked it 1 time hence the CTR % is 50.00

Solution

• First thought comes into mind is if we can calculate total number impression and total number of click for each app_id, then we can just divide the two aggregates and get the result.
• We will exactly do that.
• First we are selecting app_id, then summing all the cases where event _type is click , also summing all the cases where event_type is impression.
• Once we have these two numbers we can divide them and get the CTR , but in order to calculate % we need to multiple by 100. reason to multiply by 100.00 is that we need to avoid integer division.
• Finally we can round the CTR to 2 decimal point.
• Since we are calculating sum for each app_id , we need to pass app_id as aggregation .
• At the end we are filtering the result for year 2022 since that was the ask in the question

select app_id, 
round(sum(case when event_type = 'click' then 1 else 0 end) * 100.0 / sum(case when event_type = 'impression' then 1 else 0 end),2)
as ctr from events
where timestamp >='01/01/2022' and timestamp<'01/01/2023'
group by app_id

Submission

• Let’s submit the solution and see if its get accepted by the platform.

• It was accepted successfully.

Conclusion

• In this article we used sum aggregation function and case when operation to find the CTR rate. case when works more like if else statement that help us filtering out each row based on certain condition.
• We also used round operation to convert float number to 2 decimal point.

Bonus Tip

• If you want to upskill your SQL interview game, you can definitely check out this bestseller course