# Campaign Conversion Window

> A narrow window between impression and action.

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

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

Our ad platform logs impressions, clicks, and purchases. For each campaign, compute the click-through rate and the conversion rate as percentages rounded to 2 decimal places. A conversion means a user who clicked an ad and then made a purchase within 7 days. Only include campaigns with at least 3 impressions.

## Worked solution and explanation

### What this is really asking

CTR is a row-level rate over 800M impressions; conversion rate is distinct-user over clickers, not impressions. Two cardinalities sharing one GROUP BY is the whole problem.

---

### Break down the requirements

#### Step 1: CTR per campaign

`SUM(clicked) / COUNT(*) * 100`. CAST one side to REAL or integer division zeros out.

#### Step 2: Attribute conversions in `ON`

LEFT JOIN transactions on `user_id`, `clicked = 1`, and date within 0 to 7 days. Filter lives in `ON`, not `WHERE`, or you nullify the LEFT.

#### Step 3: Conversion denominator

Distinct clicking users, not impressions. Wrap in `NULLIF(..., 0)` so zero-clicker campaigns survive the divide.

---

### The solution

**CAMPAIGN CTR AND 7-DAY CONVERSION RATE**

```sql
SELECT
  ai.ad_campaign,
  COUNT(*) AS impressions,
  SUM(ai.clicked) AS clicks,
  ROUND(CAST(SUM(ai.clicked) AS REAL) / COUNT(*) * 100, 2) AS ctr_pct,
  COUNT(DISTINCT CASE WHEN ai.clicked = 1 AND t.transaction_id IS NOT NULL THEN ai.user_id END) AS conversions,
  ROUND(
    CAST(COUNT(DISTINCT CASE WHEN ai.clicked = 1 AND t.transaction_id IS NOT NULL THEN ai.user_id END) AS REAL)
    / NULLIF(COUNT(DISTINCT CASE WHEN ai.clicked = 1 THEN ai.user_id END), 0) * 100, 2
  ) AS conversion_rate_pct
FROM ad_impressions ai
LEFT JOIN transactions t
  ON ai.user_id = t.user_id
 AND ai.clicked = 1
 AND julianday(t.transaction_date) - julianday(date(ai.impression_time)) BETWEEN 0 AND 7
GROUP BY ai.ad_campaign
HAVING COUNT(*) >= 3
ORDER BY conversion_rate_pct DESC;
```

> **Cost Analysis**
>
> 800M LEFT JOIN 300M on `user_id` is the hot edge. Push partition predicates into the join so the planner prunes both sides. `julianday` blocks index use; a precomputed date column is cheaper at scale.

> **Interviewers Watch For**
>
> Whether the join condition lives in `ON` or `WHERE`. `clicked = 1` in `WHERE` converts the LEFT JOIN to an INNER and drops non-clicking impressions from the CTR denominator.

> **Common Pitfall**
>
> Integer division: `SUM(clicked) / COUNT(*)` is 0 without a CAST. Counting `transaction_id` instead of distinct `user_id` double-counts buyers with multiple purchases in 7 days.

> **The False Start**
>
> First instinct: join on `user_id` alone, filter `clicked = 1` in `WHERE`. Symptom: impressions fan out against every transaction, then `WHERE` collapses LEFT to INNER and non-clickers vanish. Pivot: push `clicked = 1` and the window into `ON`.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- What if a user clicks twice with one purchase between them? _(Distinct-user attribution credits the conversion once; switch to first-touch or last-touch with a window function if the spec changes.)_
- How would you extend this to revenue per click? _(Sum `t.total_amount` inside the same join filter and divide by `SUM(clicked)`.)_
- How do you scale this to a year of impressions? _(Materialize a daily `campaign_user_clicked` rollup; the 800M scan is the bottleneck.)_

## Related

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