# The Campaign Trail

> Impressions are vanity. Conversions are sanity.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The ad ops team is deciding which 2025 campaigns to renew. Which campaigns crossed the $5 total revenue threshold that year? Return their names.

## Worked solution and explanation

### Why this problem exists in real interviews

This probes `GROUP BY` with `HAVING` on an aggregated value, combined with a date range filter. It tests whether you can filter rows first, aggregate per group, then apply a threshold, which is the fundamental aggregate-filter pipeline in SQL.

---

### Break down the requirements

#### Step 1: Filter to the target year

Use a range predicate on `impression_time` to isolate the prior year. This uses partition pruning on the 365-partition table.

#### Step 2: Group by campaign

`GROUP BY ad_campaign` collapses the filtered rows into 150 campaign-level buckets.

#### Step 3: Apply the revenue threshold

`HAVING SUM(revenue) > 5` keeps only campaigns that crossed $5 in total revenue. The `HAVING` clause filters after aggregation.

---

### The solution

**Filter, aggregate, then threshold with HAVING**

```sql
SELECT ad_campaign
FROM ad_impressions
WHERE impression_time >= '2025-01-01'
  AND impression_time < '2026-01-01'
GROUP BY ad_campaign
HAVING SUM(revenue) > 5
ORDER BY ad_campaign
```

> **Cost Analysis**
>
> Partition pruning narrows the scan to one year's partitions. The `GROUP BY` reduces to 150 rows. The `HAVING` filter is applied after aggregation, so its cost is negligible. Total cost is dominated by the filtered scan.

> **Interviewers Watch For**
>
> The key differentiator is using `HAVING` instead of `WHERE` for the threshold. Candidates who try `WHERE SUM(revenue) > 5` will get a syntax error. Interviewers also watch for sargable date filters.

> **Common Pitfall**
>
> Putting the `SUM(revenue) > 5` condition in the `WHERE` clause instead of `HAVING`. Aggregate filters must go in `HAVING` because `WHERE` operates on individual rows before aggregation.

---

## Common follow-up questions

- What if you also needed the total revenue per qualifying campaign? _(Tests adding SUM(revenue) to the SELECT list alongside the HAVING filter.)_
- How would you rank the qualifying campaigns by revenue? _(Tests layering ORDER BY or a window function on top of the filtered aggregate.)_
- What if campaigns can span multiple years and you want pro-rated revenue? _(Probes date-range intersection logic for campaigns with start/end dates.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_campaign_trail)
- [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.