Ghosts in the Campaign

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

City and County of San Francisco

Problem

Some rows in ad_impressions were logged with no user we can match in users, and they still count toward a campaign's reach. Pull every impression from the holiday promo campaign with all of its fields and its user's details alongside, in the order the impressions were logged, earliest first.

The holiday sale campaign. How did it do?

Tables

ad_impressions

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

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

Worked solution and explanation

What this is really about

Beneath the campaign-report costume, this is a join that is not allowed to lose rows. The impressions are the driving set: every holiday impression is part of the campaign's reach whether or not anyone can be attributed to it. On this data five of the sixteen 'HOLIDAY_PROMO' impressions carry a NULL user_id, so the reflexive inner join to users quietly returns eleven rows and the report undercounts reach by almost a third. Anyone can write the join; the tell is choosing which side survives it.

Two quieter traps sit around it. The campaign filter is a pattern match, and LIKE compares case exactly, so the pattern has to meet the stored uppercase spelling. And 'the order the impressions were logged' means impression_time, not impression_id: the later-loaded rows carry ids above ten million with earlier timestamps.

The build

Step 1: Match the campaign as it is stored

ad_campaign holds 'HOLIDAY_PROMO' in uppercase, and LIKE is case-sensitive here, so ai.ad_campaign LIKE 'HOLIDAY%' finds it while LIKE '%holiday%' returns nothing at all. Read the sample rows before writing a pattern: the spelling on disk is the spelling the filter has to meet.

Step 2: Anchor on ad_impressions and LEFT JOIN users

A NULL user_id never equals anything, so under an inner join those five impressions have no partner and vanish. With LEFT JOIN users AS u ON ai.user_id = u.user_id they come back with username, email and the other user columns empty, which is exactly what an unattributed impression should look like.

Step 3: Sort by impression_time, not by the id

The timestamps are 'YYYY-MM-DD HH:MM:SS' text, so they sort chronologically as strings. Sorting by impression_id agrees on the first rows, then pushes the three high-id rows to the bottom even though they were logged earlier.

The solution

SELECT
  ai.impression_id,
  ai.user_id,
  ai.ad_campaign,
  ai.impression_time,
  ai.clicked,
  ai.revenue,
  u.username,
  u.email,
  u.signup_date,
  u.account_status,
  u.age_bucket
FROM ad_impressions AS ai
LEFT JOIN users AS u
  ON ai.user_id = u.user_id
WHERE ai.ad_campaign LIKE 'HOLIDAY%'
ORDER BY ai.impression_time

INNER JOIN

Eleven rows. Every impression whose user_id is NULL has no partner in users and disappears, so the campaign looks smaller than it was.

LEFT JOIN

Sixteen rows. The five unattributed impressions stay, with username, email, signup_date, account_status and age_bucket empty.

Common follow-up questions

• Now only keep impressions from the last 30 days. Where does that filter go? (A condition on the left table belongs in `WHERE`; moved into `ON` it would filter nothing and keep every impression.)
• Roll this up to total revenue for the campaign. How do the unclicked and unattributed rows affect the sum? (`SUM(revenue)` skips NULLs; the unattributed rows still contribute whatever revenue they carry.)
• If you added `WHERE u.account_status = 'active'`, what happens to the unattributed impressions? (A predicate on a right-side column rejects the NULL rows and turns the `LEFT JOIN` back into an inner one.)