# Holiday Sale Campaign Revenue

> The holiday sale campaign. How did it do?

Canonical URL: <https://datadriven.io/problems/holiday_sale_campaign_revenue>

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

### Why this problem exists in real interviews

Finding records in `ad_impressions` with no match in `users` tests the anti-join pattern. The interviewer checks whether you use LEFT JOIN + IS NULL, NOT EXISTS, or NOT IN, and understand the NULL gotcha with NOT IN.

---

### Break down the requirements

#### Step 1: Left join ad_impressions to users

`LEFT JOIN users ON user_id` preserves all rows from `ad_impressions`, including those with no match.

#### Step 2: Filter for unmatched rows

`WHERE b.user_id IS NULL` isolates rows with no corresponding record.

---

### The solution

**Anti-join with LEFT JOIN and NULL check**

```sql
SELECT a.impression_id, a.user_id, a.ad_campaign, a.impression_time
FROM ad_impressions a
LEFT JOIN users b ON a.user_id = b.user_id
WHERE b.user_id IS NULL
ORDER BY a.impression_id
```

> **Cost Analysis**
>
> The LEFT JOIN with NULL filter is the standard anti-join. An index on `users(user_id)` makes the join efficient.

> **Interviewers Watch For**
>
> The interviewer watches for the anti-join pattern choice. LEFT JOIN + IS NULL and NOT EXISTS are both correct; NOT IN has a NULL gotcha.

> **Common Pitfall**
>
> `NOT IN (SELECT user_id FROM users)` fails silently if any `user_id` in the subquery is NULL, because NOT IN with NULLs returns no rows.

---

## Common follow-up questions

- Why does NOT IN fail when the subquery contains NULLs? _(Tests three-valued logic: NULL IN (...) is NULL, so NOT NULL is still NULL.)_
- Which anti-join pattern is most performant? _(Tests LEFT JOIN and NOT EXISTS are usually equivalent; NOT IN can be slower.)_
- How would you add a date range constraint? _(Tests placing the date filter in the ON clause vs. WHERE.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/holiday_sale_campaign_revenue)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.