Calculate Weekly CTR and Campaign-Specific CTR in SQL
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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.
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.
Tables
AdEvents(ad_id INTEGER, campaign_id INTEGER, event VARCHAR, view_id INTEGER, event_date DATE)
Campaigns(campaign_id INTEGER, campaign_type VARCHAR)
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.
Community answers
Answer by SS
-- Write your SQL query here--Join ad events with campaigns With combined as(Select a. , b.campaign_typefrom adEvents aleft join campaigns b on a.campaign_id = b.campaign_id),All_As as(Select SUM(case when event= 'click' then 1 else 0 end) as clicks,SUM(Case when event = 'impression' then 1 else 0 end) as impressions, Round(SUM(case when event= 'click' then 1 else 0 end) 1.00 /NULLIF(SUM(Case when event = 'impression' then 1 else 0 end),0),2) as CTRfrom combined)
Select 'all' as compaign_type, from all_AsUNION AllSelect campaign_type ,SUM(case when event= 'click' then 1 else 0 end) as clicks,SUM(Case when event = 'impression' then 1 else 0 end) as impressions,Round(SUM(case when event= 'click' then 1 else 0 end) 1.00/NULLIF(SUM(Case when event = 'impression' then 1 else 0 end),0),2) as CTRfrom combinedgroup by 1
Answer by Xiaoming
Help