# Ghosts in the Campaign > The holiday sale campaign. How did it do? Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem Surface every impression from the holiday sale campaign along with the associated user and their revenue. Include impressions even if the user has no revenue recorded. Return all available fields for each row. ## 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. Anyone can join `ad_impressions` to `users`; the tell is whether you reach for LEFT JOIN instead of the reflexive INNER JOIN. In this campaign one impression has a null `user_id` and another points at a `user_id` that has no row in `users`, and an inner join silently deletes both. Get it wrong and your holiday report quietly undercounts the campaign, dropping exactly the impressions whose users could not be attributed. There is a second, smaller trap in the filter: the campaign names are not consistently capitalized, so an equality test against one spelling misses valid impressions. A case-insensitive substring match is what actually captures the campaign. --- ### The build #### Step 1: Match the campaign by substring, not equality Filter with ai.ad_campaign LIKE '%holiday%'. LIKE is case-insensitive for ASCII in this engine, so it catches HOLIDAY_PROMO and any other holiday-spelled variant without you hard-coding a single capitalization. An = 'HOLIDAY_PROMO' filter would work on today's data by luck and break the moment a name is cased differently. #### Step 2: Keep every qualifying impression with LEFT JOIN LEFT JOIN users ON ai.user_id = u.user_id. The impressions table is the anchor, so a null user_id or a user_id absent from users still produces a row, with the user columns coming back null. Swap in an INNER JOIN and those rows vanish from the report. #### Step 3: Order for a stable result ORDER BY ai.impression_id gives the ascending, log order the reviewer expects and makes the output deterministic across runs. --- ### The solution **LEFT JOIN preserving every impression** ```sql 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 ai LEFT JOIN users u ON ai.user_id = u.user_id WHERE ai.ad_campaign LIKE '%holiday%' ORDER BY ai.impression_id ``` **INNER JOIN (drops rows)** Returns only impressions whose user_id has a matching users row. The null-user impression and the orphaned user_id both disappear, so the campaign total silently shrinks. **LEFT JOIN (keeps rows)** Returns every holiday impression. Unmatched rows still come back, with username, email, and the rest of the user columns as null. The report reflects the whole campaign. > **Common pitfall** > > The single most common miss on this shape is reaching for INNER JOIN out of habit. Because the join key exists on both sides, it looks correct and even passes on data where every user_id matches. It only fails on the rows that matter here: nulls and orphans. When the requirement says keep every left-hand row, the join type is the answer, not a WHERE clause. > **Trick to solving** > > Filtering the campaign with an exact string match is the other quiet failure. Because casing is not standardized upstream, LIKE '%holiday%' is doing real work: it decouples your filter from however the campaign name happened to be typed. > **Interviewers watch for** > > A strong candidate says LEFT JOIN out loud and explains why the null and orphaned user_id survive it. The tell of seniority is naming the consequence of INNER JOIN before being asked: undercounted campaign reporting. --- ## Common follow-up questions - Now only count impressions from the last 30 days. Where does that date filter go, and why does it matter for the LEFT JOIN? _(Tests whether they keep the filter in WHERE, or move it into the ON clause to avoid re-dropping unmatched rows.)_ - Roll this up to total revenue for the campaign. How do the null-revenue and unmatched rows affect the sum? _(Tests aggregation with null-safe handling of the unmatched, revenue-less rows.)_ - If you added WHERE u.account_status = 'active', what happens to the unmatched impressions, and is that still a LEFT JOIN in effect? _(Tests understanding that a WHERE predicate on a right-side column turns a LEFT JOIN back into an inner one.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/ghosts_in_the_campaign) - [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.