# Campaign Click Rate > Among engaged users, which campaigns landed. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: Meta ## Problem Each row of `ad_impressions` is one ad shown to a user, with `clicked` flagging whether it was clicked, and only impressions served to users who also appear in `page_views` belong in this report. Give each campaign's clicked impressions as a percentage of those impressions, highest rate first, with campaigns on equal rates listed alphabetically. ## Tables ### ad_impressions - impression_id INTEGER - user_id INTEGER - ad_campaign TEXT - impression_time TEXT - clicked INTEGER - revenue REAL Sample rows (10 of 200): | impression_id | user_id | ad_campaign | impression_time | clicked | revenue | | --- | --- | --- | --- | --- | --- | | 253 | 100 | BRAND_AWARENESS_Q1 | 2026-02-02 01:07:00 | 0 | NULL | | 306 | 197 | PRODUCT_LAUNCH_X | 2026-03-03 02:14:00 | 0 | NULL | | 359 | 294 | RETARGETING_CART | 2026-04-04 03:21:00 | 0 | NULL | | 412 | 391 | HOLIDAY_PROMO | 2026-05-05 04:28:00 | 0 | NULL | | 465 | 488 | NEW_USER_ACQU | 2026-06-06 05:35:00 | 1 | 0.2 | | 518 | NULL | LOYALTY_PROGRAM | 2026-07-07 06:42:00 | 0 | NULL | | 571 | 682 | FLASH_SALE_48H | 2026-08-08 07:49:00 | 0 | NULL | | 624 | 779 | SUMMER_SALE_2024 | 2026-09-09 08:56:00 | 0 | NULL | | 677 | 876 | BRAND_AWARENESS_Q1 | 2026-01-10 09:03:00 | 0 | NULL | | 730 | 973 | PRODUCT_LAUNCH_X | 2026-02-11 10:10:00 | 1 | 0.35 | ### page_views - view_id INTEGER - page_url TEXT - user_id INTEGER - referrer TEXT - dur_ms INTEGER - device TEXT - viewed_at TEXT Sample rows (10 of 200): | view_id | page_url | user_id | referrer | dur_ms | device | viewed_at | | --- | --- | --- | --- | --- | --- | --- | | 5053 | /products | 1555 | amazon.com | 637 | desktop | 2026-09-02 01:09:00 | | 5106 | /checkout | 1652 | twitter.com | 774 | tablet | 2026-09-03 02:18:00 | | 5159 | /profile | 1749 | github.com | 911 | Mobile | 2026-09-04 03:27:00 | | 5212 | /search | 1846 | NULL | 1048 | DESKTOP | 2026-09-05 04:36:00 | | 5265 | /about | 1943 | reddit.com | 1185 | mobile | 2026-09-06 05:45:00 | | 5318 | /blog/intro | 1555 | NULL | NULL | desktop | 2026-09-07 06:54:00 | | 5371 | /pricing | 1652 | bing.com | 1459 | tablet | 2026-09-08 07:03:00 | | 5424 | /docs/api | 1749 | news.ycombinator.com | 1596 | Mobile | 2026-09-09 08:12:00 | | 5477 | /settings | 1846 | NULL | 1733 | DESKTOP | 2026-09-10 09:21:00 | | 5530 | /Home | 1943 | bing.com | 1870 | mobile | 2026-09-11 10:30:00 | ## Expected output First 5 of 8 rows: | ad_campaign | clicked_pct | | --- | --- | | HOLIDAY_PROMO | 50 | | PRODUCT_LAUNCH_X | 42.86 | | SUMMER_SALE_2024 | 33.33 | | FLASH_SALE_48H | 26.67 | | NEW_USER_ACQU | 26.67 | ## Worked solution and explanation ### What this really is This is a per-campaign click rate wearing a membership filter as a costume. The real skill: restrict `ad_impressions` to users who also show up in `page_views` WITHOUT letting that second table multiply your rows. Anyone can write the conditional average. The trap is reaching for an inner `JOIN page_views` to express 'also appears there': `page_views` holds roughly 50 rows per user, so each impression fans out about 50 times, and because the skew is per-user, your numerator and denominator inflate by different amounts and the whole ranking scrambles. Keep the impression grain intact with `user_id IN (SELECT user_id FROM page_views)` and the counts stay honest. --- ### Building it #### Step 1: Restrict to engaged users with a semi-join Filter `ad_impressions` down to rows whose `user_id` appears in `page_views`, using `WHERE ai.user_id IN (SELECT user_id FROM page_views)`. This is a semi-join: it tests membership and returns each impression at most once, so nothing fans out. Impressions with a `NULL` `user_id` never match and fall away on their own. #### Step 2: Count clicks over impressions per campaign `GROUP BY ai.ad_campaign`, then split each campaign's impressions into clicked and total. The numerator is `SUM(CASE WHEN ai.clicked=1 THEN 1 ELSE 0 END)` (clicked is a 0/1 flag, so you sum the flag), and the denominator is `COUNT(*)`, every qualifying impression including repeat impressions from the same user. #### Step 3: Force real division, scale, and order Both `SUM(...)` and `COUNT(*)` are integers, and integer / integer truncates toward zero, so a raw `SUM(...)/COUNT(*)` returns 0 for every campaign under 100%. `CAST(...AS REAL)` on the numerator forces real division, then `*100` scales to a percentage and `ROUND(...,2)` trims to two places. Finish with `ORDER BY clicked_pct DESC, ai.ad_campaign` so ties break alphabetically. --- ### The solution **Semi-join with a conditional click-rate** ```sql SELECT ai.ad_campaign, ROUND(CAST(SUM(CASE WHEN ai.clicked=1 THEN 1 ELSE 0 END) AS REAL)/COUNT(*)*100,2) AS clicked_pct FROM ad_impressions ai WHERE ai.user_id IN (SELECT user_id FROM page_views) GROUP BY ai.ad_campaign ORDER BY clicked_pct DESC, ai.ad_campaign ``` > **The join is not a membership test** > > Swap the semi-join for `JOIN page_views pv ON ai.user_id = pv.user_id` and every impression is duplicated once per matching page view (about 50x, unevenly per user). Both `SUM(clicked)` and `COUNT(*)` blow up by different per-user multipliers, so the percentages are wrong and the DESC ordering scrambles. `IN (SELECT ...)` or `EXISTS` tests membership without touching the row count. > **Integer division silently zeros the answer** > > Drop the `CAST` and `SUM(...)/COUNT(*)` becomes integer division: every ratio truncates to 0, so `*100` gives 0.00 across the board. Casting one operand to `REAL` before the divide is the whole difference between a real percentage and a column of zeros. > **Cost** > > The `IN (SELECT user_id FROM page_views)` semi-join lets the planner build a hash of the 600M `page_views` `user_ids` once and probe it while scanning the 400M `ad_impressions` rows, with no fan-out. An inner join would materialize the ~50x-larger intermediate before aggregating, turning a single scan into a multi-billion-row shuffle. --- ## Common follow-up questions - What changes if you also wanted the average page-view duration for those same users, per campaign? _(Tests whether they know a semi-join keeps grain while a metric on the second table needs its own aggregation.)_ - How would you show campaigns that had impressions but no engaged users, as 0%? _(Probes handling of campaigns with zero qualifying impressions and division-by-zero.)_ - How would you restrict to users who viewed at least five pages rather than any page at all? _(Tests moving from membership to a threshold, e.g. users with more than N page views.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/campaign_click_rate) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.