# Unclicked Searches by Campaign

> Searched but never clicked.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

Our ad platform logs search queries with campaign tags. Count how many search queries were performed per ad campaign, broken down by whether the user clicked a result or not. Show the campaign, click status, and event count.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests a cross-table join with a multi-column GROUP BY. Interviewers check whether you can combine search behavior data with campaign attribution and produce a breakdown by two dimensions.

---

### Break down the requirements

#### Step 1: Join searches to ad impressions

`JOIN ad_impressions ON search_queries.user_id = ad_impressions.user_id` links search activity to campaign attribution.

#### Step 2: Group by campaign and click status

`GROUP BY ad_campaign, clicked_result` with `COUNT(*)` produces the event count per campaign per click bucket.

---

### The solution

**Join and group by campaign and click status**

```sql
SELECT
    a.ad_campaign,
    s.clicked_result,
    COUNT(*) AS event_count
FROM search_queries s
JOIN ad_impressions a ON s.user_id = a.user_id
GROUP BY a.ad_campaign, s.clicked_result
ORDER BY a.ad_campaign, s.clicked_result
```

> **Cost Analysis**
>
> The join between 60M searches and 300M impressions on user_id could produce billions of rows due to the many-to-many relationship. An index on both tables' `user_id` columns is essential. Consider whether a more specific join condition (e.g., time-windowed) is needed.

> **Interviewers Watch For**
>
> Awareness that this is a many-to-many join. Each search can match multiple impressions for the same user, inflating counts. Strong candidates flag this and ask whether deduplication is needed.

> **Common Pitfall**
>
> Not recognizing the fan-out from the many-to-many join. If a user has 10 searches and 50 impressions, the join produces 500 rows for that user alone, potentially inflating the event count.

---

## Common follow-up questions

- How would you deduplicate to count each search only once per campaign? _(Use COUNT(DISTINCT s.query_id) instead of COUNT(*).)_
- What if you needed to time-correlate searches with impressions? _(Add a time window condition to the join to associate searches with nearby impressions.)_
- How would you pivot the click status into columns? _(Use CASE/SUM for conditional aggregation: SUM(CASE WHEN clicked_result = 1 ...).)_

## Related

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