# Campaigns With Most Clicks

> The campaigns getting all the clicks.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

The ad ops team is identifying which campaigns actually drive engagement. For campaigns that have received at least one click, show the campaign name, total number of clicked impressions, and the highest single-impression revenue, sorted from most clicks to least.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests filtered aggregation with HAVING and mixing aggregate functions (`COUNT` and `MAX`) in a single GROUP BY. Interviewers check that you can apply a WHERE filter before aggregation and a HAVING filter after, and that you know how to sort descending.

---

### Break down the requirements

#### Step 1: Filter to clicked impressions

`WHERE clicked = 1` restricts to impressions that resulted in a click before aggregation.

#### Step 2: Group and aggregate

`GROUP BY ad_campaign` with `COUNT(*)` for click count and `MAX(revenue)` for highest single-impression revenue.

#### Step 3: Sort by click count descending

`ORDER BY COUNT(*) DESC` surfaces the most-clicked campaigns first. The HAVING clause is implicit via the WHERE filter: campaigns with zero clicks produce no rows.

---

### The solution

**Filtered aggregation with dual metrics**

```sql
SELECT
    ad_campaign,
    COUNT(*) AS total_clicks,
    MAX(revenue) AS max_impression_revenue
FROM ad_impressions
WHERE clicked = 1
GROUP BY ad_campaign
ORDER BY total_clicks DESC
```

> **Cost Analysis**
>
> The WHERE filter on `clicked = 1` typically eliminates 90%+ of rows (click-through rates are low), reducing the aggregation input significantly. On 450M rows, a partial index on `clicked` or a filtered index `WHERE clicked = 1` would accelerate this query.

> **Interviewers Watch For**
>
> Candidates who unnecessarily add `HAVING COUNT(*) >= 1` when the WHERE clause already guarantees at least one click per group show a misunderstanding of how WHERE interacts with GROUP BY.

> **Common Pitfall**
>
> Using `MAX(revenue)` on the full table instead of only clicked impressions would give the wrong answer. The WHERE filter must come before the aggregation.

---

## Common follow-up questions

- How would you also show the campaign with zero clicks? _(Tests LEFT JOIN from a distinct campaigns subquery or conditional aggregation.)_
- What if you needed the top 5 campaigns only? _(Tests LIMIT vs DENSE_RANK depending on tie requirements.)_
- How would performance change if click-through rates were 50% instead of sub-1%? _(The WHERE filter becomes less selective, making index scans less beneficial.)_

## Related

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