Back to questions
App Click-through Rate (CTR) [Facebook SQL Interview Question]
This is the same question as problem #1 in the SQL Chapter of Ace the Data Science Interview!
Assume you have an events table on app analytics. Write a query to get the app’s click-through rate (CTR %) in 2022. Output the results in percentages rounded to 2 decimal places.
Notes:
- Percentage of click-through rate = 100.0 * Number of clicks / Number of impressions
- To avoid integer division, you should multiply the click-through rate by 100.0, not 100.
Table:
| Column Name | Type |
|---|---|
| app_id | integer |
| event_type | string |
| timestamp | datetime |
Example Input:
| app_id | event_type | timestamp |
|---|---|---|
| 123 | impression | 07/18/2022 11:36:12 |
| 123 | impression | 07/18/2022 11:37:12 |
| 123 | click | 07/18/2022 11:37:42 |
| 234 | impression | 07/18/2022 14:15:12 |
| 234 | click | 07/18/2022 14:16:12 |
Example Output:
| app_id | ctr |
|---|---|
| 123 | 50.00 |
| 234 | 100.00 |
Explanation
App 123 has a CTR of 50.00% because this app receives 1 click out of the 2 impressions. Hence, it's 1/2 = 50.00%.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Before we proceed, let's list down the steps to solve the question.
- Filter for analytics events from the year 2022.
- Find the number of clicks and number of impressions.
- Calculate the percentage of the click-through rate.
First, we filter for events from the year 2022 in the clause by using the appropriate comparison operators.
- refers to the events on 2022-01-01 and later.
- means events before 2023-01-01 and does not include 2023-01-01.
Next, we want to find the number of clicks and number of impressions using the statement and how you can interpret it:
If the event_type is 'click', then assign the value of 1. Otherwise, assign the value of 0.
Showing the first 5 rows of output:
| app_id | clicks | impressions |
|---|---|---|
| 123 | 0 | 1 |
| 123 | 0 | 1 |
| 123 | 1 | 0 |
| 234 | 0 | 1 |
| 234 | 1 | 0 |
Next, we have to add up the clicks and impressions by simply wrapping the CASE statements with a function.
| app_id | clicks | impressions |
|---|---|---|
| 123 | 2 | 3 |
| 234 | 1 | 3 |
Based on the formula below, we have to divide the number of clicks by the number of impressions and then multiply by 100.0.
Percentage of click-through rate = 100.0 * Number of clicks / Number of impressions
Remember to use the function to round up the percentage results to 2 decimal places.
Solution #1: Using SUM(CASE ...)
Solution #2: Using COUNT(CASE ...)
Solution #3: Using SUM() FILTER ()
PostgreSQL 14