# Holiday Promo Campaign Click Year

> One year, the holiday campaign exploded.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The ad impressions table logs campaign-level clicks. Find the year in which the HOLIDAY_PROMO campaign had at least 1 total click. Roll up impressions by year for the HOLIDAY_PROMO campaign only. Return the qualifying fiscal year.

## Worked solution and explanation

### What this is really asking

`clicked` is a 0/1 flag, so `SUM(clicked)` is total clicks. Pull `strftime('%Y', impression_time)` per row, filter to HOLIDAY_PROMO, group by year, keep years where the sum is at least 1.

---

### Break down the requirements

#### Step 1: Filter the campaign early

Push `ad_campaign = 'HOLIDAY_PROMO'` into `WHERE` so the 150M-row table prunes before aggregation.

#### Step 2: Bucket and filter

Group by `strftime('%Y', impression_time)` (SQLite needs the expression repeated, not an alias). `HAVING SUM(clicked) >= 1` keeps only years with a real click.

---

### The solution

**HOLIDAY PROMO CLICK YEARS**

```sql
SELECT strftime('%Y', impression_time) AS fiscal_year
FROM ad_impressions
WHERE ad_campaign = 'HOLIDAY_PROMO'
GROUP BY strftime('%Y', impression_time)
HAVING SUM(clicked) >= 1
```

> **Cost Analysis**
>
> Partition pruning is the win. Avoid wrapping `impression_time` in a function inside `WHERE`, since that disables pruning on the partition key.

> **Interviewers Watch For**
>
> Summing the boolean flag rather than counting rows, repeating the `strftime` expression in `GROUP BY` (SQLite rejects aliases there), and using `HAVING` for the click threshold.

> **Common Pitfall**
>
> `COUNT(clicked)` counts every non-null row, click or not, so it returns every year with impressions. Use `SUM(clicked)` to count actual clicks.

> **The False Start**
>
> First instinct is `WHERE ad_campaign = 'HOLIDAY_PROMO' AND clicked = 1`, then `SELECT DISTINCT year`. That works, but discards rows that could answer follow-ups like total clicks per year. Pivot to `SUM(clicked)` with `HAVING` so the shape extends.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you also return total clicks and click-through rate per year? _(Add `SUM(clicked) AS clicks` and `1.0 * SUM(clicked) / COUNT(*) AS ctr` to the SELECT.)_
- What if fiscal year does not align with calendar year? _(Replace `strftime('%Y', impression_time)` with a CASE on month, or join a `fiscal_calendar` dimension.)_
- How would you guard against `clicked` being NULL? _(`SUM` ignores NULLs, but if NULL means unknown, wrap in `COALESCE(clicked, 0)` so the count reflects observed events.)_

## Related

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