Click-Through by Campaign

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

Airbnb

Problem

A campaign's clicked and not-clicked shares are percentages of that campaign's own impressions, kept to two decimal places. List every campaign in ad_impressions alphabetically with its total impressions and both shares.

Which campaigns actually got the tap.

Tables

ad_impressions

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

push_notifs

Columns: notif_id INTEGER, user_id INTEGER, title TEXT, platform TEXT, status TEXT, sent_at TEXT, opened INTEGER, campaign TEXT

Expected output

Worked solution and explanation

This is a one-pass pivot of a 0/1 flag dressed up as an engagement report. The pivot is not what separates candidates: the division is. clicked is an integer, a SUM over a CASE is an integer, and COUNT(*) is an integer, and integer over integer floors. Write SUM(...) * 100 / COUNT(*) and the 3 clicks out of 17 impressions for 'BRAND_AWARENESS_Q1' come back as 17, not 17.65. Every share in the report loses its decimals, and the two shares of a campaign stop adding to 100.

Walking to the query

Step 1: Read only ad_impressions

push_notifs has a campaign column of its own, and that is the bait. Its values ('loyalty_2024', 'flash_sale') are not the ad campaigns. Joining it fans every impression out across notification rows and inflates total_impressions. Everything this report needs lives on the impression row.

Step 2: One row per campaign, one denominator

GROUP BY ad_campaign collapses the log to eight rows, and COUNT(*) is the denominator for both shares. Use COUNT(*), not COUNT(revenue): revenue is NULL on every unclicked impression, so COUNT(revenue) quietly counts clicks and both shares come out wrong.

Step 3: Pivot the flag into two counts

SUM(CASE WHEN clicked = 1 THEN 1 ELSE 0 END) counts the clicks inside the same group, and the mirrored clicked = 0 branch counts the misses. Both counts sit side by side on the campaign row, and there is no second pass or self-join.

Step 4: Make the numerator real before you divide

CAST(... AS REAL) turns the click count into a real number, so * 100 / COUNT(*) keeps its fraction. ROUND(..., 2) then trims it to the two decimals the report shows. Converting one operand before the division is enough; converting after it is too late.

SELECT
    ad_campaign,
    COUNT(*) AS total_impressions,
    ROUND(CAST(SUM(CASE WHEN clicked = 1 THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(*), 2) AS clicked_pct,
    ROUND(CAST(SUM(CASE WHEN clicked = 0 THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(*), 2) AS not_clicked_pct
FROM ad_impressions
GROUP BY ad_campaign
ORDER BY ad_campaign

Split by the flag

GROUP BY ad_campaign, clicked with COUNT(*) gives up to two rows per campaign, holding raw counts and no shares. You would still need a second step to bring the campaign total back onto each row.

Pivot the flag

GROUP BY ad_campaign with two conditional sums puts both counts and their shared COUNT(*) on one row, so each share is a single expression over the same group.

Common follow-up questions

• How would you report the same shares per campaign per month of `impression_time`? (Adds a time grain to the group without changing the denominator logic.)
• What if `clicked` could be NULL for impressions whose tracking pixel never fired? (Tests whether NULL is treated as not clicked, excluded from the denominator, or reported as its own share.)
• How would you add each campaign's push open rate when `push_notifs` uses different campaign names? (Now the second table matters: it needs a mapping and aggregation on each side before any join.)