# Click-Through by Campaign

> Which campaigns actually got the tap.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The ad team wants an engagement breakdown by campaign. For each campaign, show the total number of impressions and the percentage that were clicked versus not clicked. List alphabetically by campaign.

## Worked solution and explanation

### Why this problem exists in real interviews

This probes conditional aggregation, a core skill for building pivot-style reports in a single pass. Interviewers want to see that you can split a metric by a binary flag using `CASE WHEN` inside aggregate functions rather than writing multiple queries or subqueries.

---

### Break down the requirements

#### Step 1: Identify the relevant table

Group ad_impressions by ad_campaign.

#### Step 2: Group by campaign

Count total impressions per campaign with COUNT(*).

#### Step 3: Compute clicked vs non-clicked splits

Use conditional SUM(CASE WHEN clicked=1/0) over the count for the clicked vs non-clicked percentage split.

#### Step 4: Order alphabetically

Order alphabetically by campaign.

---

### The solution

**Conditional aggregation with percentage split**

```sql
SELECT ad_campaign, COUNT(*) AS total_impressions, ROUND(CAST(SUM(CASE WHEN clicked=1 THEN 1 ELSE 0 END) AS REAL)*100.0/COUNT(*),2) AS clicked_pct, ROUND(CAST(SUM(CASE WHEN clicked=0 THEN 1 ELSE 0 END) AS REAL)*100.0/COUNT(*),2) AS not_clicked_pct FROM ad_impressions GROUP BY ad_campaign ORDER BY ad_campaign
```

> **Cost Analysis**
>
> A single sequential scan of 250M rows with no joins. The `GROUP BY` reduces output to ~180 rows. This is as efficient as possible; the bottleneck is I/O on the full table scan. An index on `ad_campaign` would not help since we need every row.

> **Interviewers Watch For**
>
> Candidates who recognize the `push_notifs` table as a distractor show strong prompt-reading skills. The other key signal is using conditional aggregation in one pass rather than self-joining the table twice.

> **Common Pitfall**
>
> Dividing by `SUM(revenue)` without guarding against zero-revenue campaigns causes division-by-zero errors. Wrapping in `NULLIF(SUM(revenue), 0)` is the safe pattern.

---

## Common follow-up questions

- How would you handle campaigns with zero total revenue? _(Tests NULLIF or CASE guards around division to prevent runtime errors.)_
- What if the prompt also asked for the push notification open rate per campaign? _(Now the second table is relevant; tests JOIN strategy and grain alignment.)_
- Could you produce this as a pivoted result with separate rows for clicked and non-clicked? _(Tests UNION ALL vs conditional aggregation trade-offs.)_

## Related

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