Compute CTR overall and by campaign type
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Write SQL to compute: (Q1) overall click-through rate (CTR = clicks/impressions) in the last week; (Q2) CTR by campaign_type in the last week. Assume 'today' is 2025-09-01; define 'last week' as 2025-08-25 00:00:00 to 2025-08-31 23:59:59 (UTC). Deduplicate identical logs by (ad_id, view_id, event) within the window. Prevent division-by-zero. Schema and sample data:
Tables
- ad_events(ad_id INT, campaign_id INT, event STRING, view_id STRING, event_time TIMESTAMP) -- event in {'impression','click'}
- campaigns(campaign_id INT, campaign_type STRING) -- campaign_type in {'direct_response','brand'}
Sample rows (minimal):
ad_events
ad_id | campaign_id | event | view_id | event_time
101 | 1 | impression | v1001 | 2025-08-28 10:00:00
101 | 1 | click | v1001 | 2025-08-28 10:00:05
102 | 2 | impression | v1002 | 2025-08-30 12:00:00
102 | 2 | impression | v1003 | 2025-08-30 12:05:00
103 | 2 | click | v1002 | 2025-08-30 12:00:07
104 | 3 | impression | v1004 | 2025-08-26 08:00:00
campaigns
campaign_id | campaign_type
1 | direct_response
2 | brand
3 | direct_response
Q1 (overall CTR): return a single row with overall_ctr.
Q2 (by type): return campaign_type, clicks, impressions, ctr.
Edge cases: if impressions=0, return ctr=0.0; only count events inside the window; treat multiple logs of the same (ad_id, view_id, event) as one.
Example solution sketch (you may adapt to your SQL dialect):
WITH dedup AS (
SELECT DISTINCT ad_id, campaign_id, view_id, event
FROM ad_events
WHERE event_time >= '2025-08-25' AND event_time < '2025-09-01 24:00:00'
),
imp AS (SELECT COUNT(*) AS imps FROM dedup WHERE event='impression'),
clk AS (SELECT COUNT(*) AS clks FROM dedup WHERE event='click')
SELECT CASE WHEN imps=0 THEN 0.0 ELSE clks*1.0/imps END AS overall_ctr
FROM imp CROSS JOIN clk;
-- By campaign_type
WITH dedup AS (
SELECT DISTINCT ad_id, campaign_id, view_id, event
FROM ad_events
WHERE event_time >= '2025-08-25' AND event_time < '2025-09-01 24:00:00'
),
agg AS (
SELECT campaign_id,
SUM(CASE WHEN event='impression' THEN 1 ELSE 0 END) AS imps,
SUM(CASE WHEN event='click' THEN 1 ELSE 0 END) AS clks
FROM dedup
GROUP BY campaign_id
)
SELECT c.campaign_type,
COALESCE(a.clks,0) AS clicks,
COALESCE(a.imps,0) AS impressions,
CASE WHEN COALESCE(a.imps,0)=0 THEN 0.0 ELSE COALESCE(a.clks,0)*1.0/COALESCE(a.imps,0) END AS ctr
FROM campaigns c
LEFT JOIN agg a ON a.campaign_id = c.campaign_id
ORDER BY c.campaign_type;
Overview: This question evaluates data manipulation and analytics skills, specifically SQL aggregations, deduplication of event logs, time-window filtering, joins to campaign metadata, and safe ratio computation including division-by-zero handling.
Compute overall CTR in a given week with deduplicated ad logs
Using the tables below, write SQL to compute the overall click-through rate (CTR = clicks / impressions) for all ads in the week from 2025-08-25 00:00:00 to 2025-08-31 23:59:59 (UTC).
Requirements:
- Only count events whose event_time falls inside the window 2025-08-25 00:00:00 to 2025-08-31 23:59:59 (inclusive).
- Deduplicate identical logs within the window: treat multiple rows with the same (ad_id, view_id, event) as a single event.
- CTR is defined as total_clicks / total_impressions after deduplication.
- Prevent division by zero: if there are zero impressions in the window, return overall_ctr = 0.0.
Return a single row with one column: overall_ctr.
Tables
ad_events(ad_id INT, campaign_id INT, event VARCHAR(20), view_id VARCHAR(50), event_time TIMESTAMP)
campaigns(campaign_id INT, campaign_type VARCHAR(50))
Hints
- First deduplicate events with a DISTINCT on the key (ad_id, campaign_id, view_id, event) within the time window.
- Aggregate clicks and impressions in a CTE or subquery, then compute clicks / impressions with a CASE expression to handle zero impressions.
Compute CTR by campaign type with deduplicated ad logs
Using the same tables, write SQL to compute click-through rate (CTR) by campaign_type for the week from 2025-08-25 00:00:00 to 2025-08-31 23:59:59 (UTC).
Requirements:
- Only count events whose event_time falls inside the window 2025-08-25 00:00:00 to 2025-08-31 23:59:59 (inclusive).
- Deduplicate identical logs within the window: treat multiple rows with the same (ad_id, view_id, event) as a single event.
- For each campaign_type, compute:
- clicks: total number of click events after deduplication.
- impressions: total number of impression events after deduplication.
- ctr: clicks / impressions.
- If impressions = 0 for a campaign_type, return ctr = 0.0 for that type.
Return one row per campaign_type with columns: campaign_type, clicks, impressions, ctr.
Tables
ad_events(ad_id INT, campaign_id INT, event VARCHAR(20), view_id VARCHAR(50), event_time TIMESTAMP)
campaigns(campaign_id INT, campaign_type VARCHAR(50))
Hints
- Start from the same deduplicated events CTE you would use for the overall CTR, but join it to the campaigns table.
- Aggregate clicks and impressions grouped by campaign_type, then compute CTR with a CASE expression to avoid dividing by zero.