Calculate Weekly CTR and Campaign-Specific CTR in SQL

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Meta.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Meta during technical interviews.

Question

AdEvents

ad_id | campaign_id | event        | view_id | event_date

1     | 10          | impression   | 123     | 2023-11-07

1     | 10          | click        | 123     | 2023-11-07

2     | 11          | impression   | 124     | 2023-11-07

3     | 12          | impression   | 125     | 2023-11-07

3     | 12          | click        | 125     | 2023-11-07

​

Campaigns

campaign_id | campaign_type

10          | direct_response

11          | brand

12          | direct_response

##### Scenario

You have event-level logs of ad impressions and clicks plus a lookup of campaign types. Management wants click-through-rate (CTR) numbers for last week.

##### Question

Write SQL to compute overall CTR (clicks / impressions) across all ads for the last calendar week. Write SQL to compute CTR broken out by campaign_type for the same period.

##### Hints

Filter by event_date, join tables, count impressions and clicks separately, then divide using CAST to avoid integer division.

PracHub · Accepted Answer

This question evaluates a candidate's ability to compute advertising metrics from event-level logs using SQL, specifically aggregating impressions and clicks and calculating click-through rates across and by campaign dimensions.

Quick Overview

Quick Overview