# Click Revenue

> Which campaigns are earning their keep?

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The ad operations team is evaluating campaign ROI for the monthly review. For each ad campaign, calculate the total revenue generated exclusively from impressions that resulted in a click. Only include campaigns that brought in more than five dollars in click revenue, and rank them from highest total to lowest.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests filtered aggregation with HAVING on a dollar threshold. It verifies that you can apply a WHERE filter before aggregation, compute a sum, apply a post-aggregation filter, and sort.

---

### Break down the requirements

#### Step 1: Filter to clicked impressions

`WHERE clicked = 1` restricts to click-attributed revenue.

#### Step 2: Sum revenue per campaign

`GROUP BY ad_campaign` with `SUM(revenue)` computes total click revenue.

#### Step 3: Filter and sort

`HAVING SUM(revenue) > 5` removes low-revenue campaigns. `ORDER BY SUM(revenue) DESC` ranks highest first.

---

### The solution

**Filtered sum with HAVING threshold**

```sql
SELECT ad_campaign, SUM(revenue) AS click_revenue
FROM ad_impressions
WHERE clicked = 1
GROUP BY ad_campaign
HAVING SUM(revenue) > 5
ORDER BY click_revenue DESC
```

> **Cost Analysis**
>
> Full scan of 2B rows with WHERE filter reducing to ~20M clicked rows (1% CTR). GROUP BY on ~200 campaigns is trivial. The scan dominates.

> **Common Pitfall**
>
> Putting the `clicked = 1` filter in HAVING instead of WHERE would still work for correctness (with conditional SUM), but it forces the engine to scan all 2B rows through the GROUP BY. WHERE filters first, reducing work.

---

## Common follow-up questions

- What if revenue could be negative (refunds)? _(SUM would net them out; you might need to filter or separate refunds.)_
- How would you also show non-click revenue alongside click revenue? _(Tests conditional aggregation with CASE WHEN in a single GROUP BY.)_
- What if the threshold was dynamic, like the top 50% of campaigns by revenue? _(Tests PERCENTILE_CONT or subquery-based thresholds.)_

## Related

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