logo

Back to questions

App Click-through Rate (CTR) Facebook SQL Interview Question

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 Facebook app analytics. Write a query to calculate the click-through rate (CTR) for the app in 2022 and round the results to 2 decimal places.

Definition and note:

  • Percentage of click-through rate (CTR) = 100.0 * Number of clicks / Number of impressions
  • To avoid integer division, multiply the CTR by 100.0, not 100.

Table:

Column NameType
app_idinteger
event_typestring
timestampdatetime

Example Input:

app_idevent_typetimestamp
123impression07/18/2022 11:36:12
123impression07/18/2022 11:37:12
123click07/18/2022 11:37:42
234impression07/18/2022 14:15:12
234click07/18/2022 14:16:12

Example Output:

app_idctr
12350.00
234100.00

Explanation

Let's consider an example of App 123. This app has a click-through rate (CTR) of 50.00% because out of the 2 impressions it received, it got 1 click.

To calculate the CTR, we divide the number of clicks by the number of impressions, and then multiply the result by 100.0 to express it as a percentage. In this case, 1 divided by 2 equals 0.5, and when multiplied by 100.0, it becomes 50.00%. So, the CTR of App 123 is 50.00%.

The dataset you are querying against may have different input & output - this is just an example!

Input

(PostgreSQL)

Output