logo

Back to questions

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

Easy

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 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

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!

PostgreSQL 14