# Break Through

> A click is the only vote that counts.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

The marketing team is auditing ad campaigns before next quarter's budget is set, and a campaign is only worth renewing when more than one in five impressions turns into a click. Surface those campaigns with their click-through rate and the revenue they brought in, highest click-through rate first.

## Worked solution and explanation

### Why this problem exists in real interviews

Underneath the marketing language this is a ratio filtered by its own value. The metric you sort and threshold on, SUM(clicked) / COUNT(*), does not exist until after the grouping, so the filter has to run on the aggregate, not on individual rows. Two ways candidates blow it: they reach for WHERE (evaluated before the grouping, so it cannot see the ratio at all), or they write SUM(clicked) / COUNT(*) as plain integer division, which floors every sub-100% campaign to 0 and quietly filters the entire result set to nothing.

---

### Break down the requirements

#### Step 1: Aggregate per campaign

Group ad_impressions by ad_campaign so every metric is computed per campaign.

#### Step 2: Compute CTR

Compute click-through rate as a percentage: 100.0 * SUM(clicked) / COUNT(*), plus SUM(revenue) and COUNT(*) for total revenue and impressions served.

#### Step 3: Filter and sort

Keep only campaigns where CTR exceeds 20% ('more than one in five impressions resulted in a click') via HAVING on the unrounded ratio, then order by CTR descending.

---

### The solution

**CTR computation with threshold filter**

```sql
SELECT
    ad_campaign,
    ROUND(100.0 * SUM(clicked) / COUNT(*), 2) AS ctr_pct,
    SUM(revenue) AS total_revenue,
    COUNT(*) AS impressions
FROM ad_impressions
GROUP BY ad_campaign
HAVING 100.0 * SUM(clicked) / COUNT(*) > 20
ORDER BY ctr_pct DESC, ad_campaign
```

> **Cost Analysis**
>
> Full scan of 2B rows with single-pass aggregation. The GROUP BY reduces to ~200 campaigns. The HAVING filter runs on the aggregated output and is negligible. The I/O on 2B rows is the bottleneck.

> **Interviewers Watch For**
>
> The tell is the `100.0 *` in front of the division. Writing it forces floating-point division; leaving it off gives integer division that truncates every sub-100% CTR to 0. A senior candidate reaches for it reflexively.

> **Common Pitfall**
>
> Using integer division `SUM(clicked) / COUNT(*)` returns 0 for any CTR below 100%, so the HAVING > 20 filter drops every campaign and the query returns nothing. Always multiply by `100.0` (or cast to NUMERIC) before dividing.

---

## Common follow-up questions

- What if the HAVING threshold was the average CTR across all campaigns? _(Tests using a CTE or subquery to compute the global average and reference it in HAVING.)_
- How would you handle campaigns with zero impressions? _(Division by zero guard using NULLIF(COUNT(*), 0).)_
- What if you needed weekly CTR trends per campaign? _(Tests adding a date truncation to GROUP BY for time-series analysis.)_

## Related

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