Campaign Click Rate

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

Among engaged users, which campaigns landed.

Tables

ad_impressions

Columns: impression_id INTEGER, user_id INTEGER, ad_campaign TEXT, impression_time TEXT, clicked INTEGER, revenue REAL

page_views

Columns: view_id INTEGER, page_url TEXT, user_id INTEGER, referrer TEXT, dur_ms INTEGER, device TEXT, viewed_at TEXT

Expected output

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

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

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.)