Question: Assume you have the below ‘events’ table on app analytics. Write a query to get the click-through rate (CTR) per app in 2019.
Difficulty Level: Easy, Source: NickSingh.com, Tag: Facebook SQL Interview Question
Note: CTR is the number of clicks that your ad receives divided by the number of times your ad is shown. i.e CTR = total_clicks/total_ads_served (impressions)
Assumptions: No NULLS present; DBMS: Spark SQL
Approach:
Basic Explanation: A given event_id is a string, that could either be an impression or a click. Any event until it was clicked will be considered as an impression. Note that since an event tagged as click was an impression before the click happened, hence the total number of times ads were shown should be the sum of all impression and click events.
Step-1: Understand that the question asks for information on app level, which means aggregation has to be done on app_id.
Step-2: Count the number of events with event_id value as ‘impression’ and divide by the total number of events and that would give us the CTR value.
Solution:
select app_id, sum(case when event_id = 'click' then 1 else 0 end)/count(event_id) as CTR from events where year(TIMESTAMP) = 2019 group by app_id