Campaign Conversion Window

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

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

Workday

Problem

Report each ad campaign's click-through rate and conversion rate from ad_impressions and transactions, both as decimal percentages rounded to two places, next to the impression, click and converter counts behind them, counting only impressions tied to a known user. A converter is a unique user who clicked one of the campaign's ads and bought something on that impression's date or within the 7 days after, measured against the campaign's unique clickers, and the rate stays blank for a campaign that converted no one. Only campaigns with at least 4 clicks belong in the report.

A narrow window between impression and action.

Tables

ad_impressions

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

transactions

Columns: transaction_id INTEGER, user_id INTEGER, transaction_date TEXT, total_amount REAL, product_id INTEGER, quantity INTEGER

Expected output

Worked solution and explanation

What this is really asking

This is two rates at two different grains sharing one GROUP BY, wearing a marketing-attribution costume. CTR is a ratio over impression rows; the conversion rate is a ratio over unique clickers. The trap is the 7-day rule: it is a join condition, not a filter. The moment clicked = 1 or the date window moves into WHERE, the LEFT JOIN collapses to an inner one and every campaign that converted no one silently disappears from the report.

Break down the requirements

Step 1: Count impressions before purchases multiply them

A clicked impression with two purchases in its window leaves the join as two rows, so COUNT(*) and SUM(ai.clicked) over the joined rows overcount exactly the campaigns that sell. Unclicked impressions never match a purchase, so SUM(CASE WHEN ai.clicked = 0 THEN 1 ELSE 0 END) counts them safely, one row each. Clicked impressions are counted by impression_id with COUNT(DISTINCT ...). Their sum is the impression count, and the clicked part alone is the click count, the CTR numerator and the 4-click bar.

Step 2: Attribute conversions in ON

LEFT JOIN transactions on user_id, with clicked = 1 and the 0-to-7-day window living in the ON clause. date(impression_time) drops the clock so the window is measured in whole days against transaction_date. Put either condition in WHERE and the join turns inner, dropping every zero-conversion campaign.

Step 3: Drop anonymous impressions in WHERE

Some impressions carry no user_id. They can never be attributed to a buyer, and the report counts only impressions tied to a known user, so WHERE ai.user_id IS NOT NULL removes them before anything is counted. This filter belongs in WHERE because it is about the impression itself, not the join: it shrinks COUNT(*), moves CTR, and can move a campaign across the 4-click bar.

Step 4: Blank the rate, not the count

100.0 * conversions / clickers. The float literal makes the division decimal, the same job the CAST does for CTR. Wrap the converter count in NULLIF(..., 0) so a campaign that converted no one reports a blank rate instead of a flat 0.00% that reads like a measured floor. The raw conversions column still shows 0; only the rate is blanked.

The solution

SELECT
  ai.ad_campaign,
  SUM(CASE WHEN ai.clicked = 0 THEN 1 ELSE 0 END)
    + COUNT(DISTINCT CASE WHEN ai.clicked = 1 THEN ai.impression_id END) AS impressions,
  COUNT(DISTINCT CASE WHEN ai.clicked = 1 THEN ai.impression_id END) AS clicks,
  ROUND(
    CAST(COUNT(DISTINCT CASE WHEN ai.clicked = 1 THEN ai.impression_id END) AS REAL)
      / (SUM(CASE WHEN ai.clicked = 0 THEN 1 ELSE 0 END)
         + COUNT(DISTINCT CASE WHEN ai.clicked = 1 THEN ai.impression_id END)) * 100,
    2
  ) AS ctr_pct,
  COUNT(DISTINCT CASE WHEN t.transaction_id IS NOT NULL THEN ai.user_id END) AS conversions,
  ROUND(
    100.0 * NULLIF(COUNT(DISTINCT CASE WHEN t.transaction_id IS NOT NULL THEN ai.user_id END), 0)
      / COUNT(DISTINCT CASE WHEN ai.clicked = 1 THEN ai.user_id END),
    2
  ) AS conversion_rate_pct
FROM ad_impressions ai
LEFT JOIN transactions t
  ON t.user_id = ai.user_id
 AND ai.clicked = 1
 AND julianday(t.transaction_date) - julianday(date(ai.impression_time)) BETWEEN 0 AND 7
WHERE ai.user_id IS NOT NULL
GROUP BY ai.ad_campaign
HAVING COUNT(DISTINCT CASE WHEN ai.clicked = 1 THEN ai.impression_id END) >= 4;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you write this with one row per impression before the rollup? (Collapse each impression to a single row with a converted flag first; the campaign rollup then uses plain `COUNT(*)` and `SUM(clicked)` with no fan-out to defend against.)
• How would you extend this to revenue per click? (Sum `t.total_amount` inside the same join predicate and divide by the deduplicated click count, not a joined-row count.)
• How do you scale this to a year of impressions? (Materialize a daily `campaign_user_clicked` rollup; the 800M-row scan is the bottleneck.)