Coding questions · Meta
Calculate Weekly CTR and Campaign-Specific CTR in SQL
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.
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.
Compute CTR overall and by campaign type for a specific week
You are given event-level logs of ad impressions and clicks plus a lookup of campaign types. Compute click-through-rate (CTR = clicks / impressions) for the calendar week from 2025-05-19 to 2025-05-25 (inclusive). Return a result set that includes one row for the overall CTR across all campaigns (labeled as 'ALL') and one row per campaign_type for the same period, with columns for clicks, impressions, and CTR.
Table AdEvents
| column | type | constraints |
|---|---|---|
| ad_id | INTEGER | |
| campaign_id | INTEGER | |
| event | VARCHAR | |
| view_id | INTEGER | |
| event_date | DATE |
Sample data:
| ad_id | campaign_id | event | event_date | view_id |
|---|---|---|---|---|
| 1 | 10 | impression | 2025-05-20 | 123 |
| 1 | 10 | click | 2025-05-20 | 123 |
| 2 | 11 | impression | 2025-05-20 | 124 |
| 3 | 12 | impression | 2025-05-20 | 125 |
| 3 | 12 | click | 2025-05-20 | 125 |
Table Campaigns
| column | type | constraints |
|---|---|---|
| campaign_id | INTEGER | |
| campaign_type | VARCHAR |
Sample data:
| campaign_id | campaign_type |
|---|---|
| 10 | direct_response |
| 11 | brand |
| 12 | direct_response |
Expected output
For the calendar week 2025-05-19 to 2025-05-25, return one row labeled 'ALL' for overall CTR across all campaigns and one row per campaign_type, each with clicks, impressions, and CTR = clicks / impressions.
| campaign_type | clicks | ctr | impressions |
|---|---|---|---|
| ALL | 2 | 0.6667 | 3 |
| brand | 0 | 0 | 1 |
| direct_response | 2 | 1 | 2 |
Hints
- Filter AdEvents by event_date between '2025-05-19' and '2025-05-25' (inclusive, e.g. event_date >= DATE '2025-05-19' AND event_date < DATE '2025-05-26').
- Join AdEvents to Campaigns on campaign_id to get campaign_type for each event.
- Use conditional aggregation to count impressions and clicks separately in the same query.
- Cast counts to a decimal type and use NULLIF in the denominator to avoid integer division and divide-by-zero errors.