Back to questions
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:
Column Name | Type |
---|---|
app_id | integer |
event_type | string |
timestamp | datetime |
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 |
app_id | ctr |
---|---|
123 | 50.00 |
234 | 100.00 |
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!
Before we proceed, let's list down the steps to solve the question.
First, we filter for events from the year 2022 in the clause by using the appropriate comparison operators.
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