# Click Rate

> Campaigns nobody clicks.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

The marketing team is auditing ad campaign performance before next quarter's budget allocation. For each campaign, they want to see the click-through rate as a percentage of impressions, the total revenue attributed to the campaign, and how many impressions were served. Only surface campaigns with more than one in five impressions resulted in a click ,  anything below that isn't worth renewing. Rank from highest click-through rate to lowest.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests percentage computation with HAVING on the derived metric. Computing click-through rate (CTR) requires dividing a conditional count by a total count, and the threshold filter is on the computed percentage, not a raw column.

---

### Break down the requirements

#### Step 1: Aggregate per campaign

`GROUP BY ad_campaign` with `COUNT(*)` for impressions, `SUM(clicked)` for click count, and `SUM(revenue)` for total revenue.

#### Step 2: Compute CTR

`100.0 * SUM(clicked) / COUNT(*)` gives click-through rate as a percentage.

#### Step 3: Filter and sort

`HAVING 100.0 * SUM(clicked) / COUNT(*) > 20` keeps campaigns above 20% CTR. `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
```

> **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**
>
> Whether the candidate computes the percentage correctly: `100.0 *` forces floating-point division (integer division would truncate to 0 for low CTRs). This is a subtle but critical detail.

> **Common Pitfall**
>
> Using integer division `SUM(clicked) / COUNT(*)` returns 0 for any CTR below 100%. 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/click_rate)
- [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.