# Campaign Engagement Rank Shift

> Two months, many countries. Who moved up? Who fell?

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

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

A stakeholder flagged that some campaigns lost momentum between June 2025 and October 2025. Rank each campaign by the number of impressions where clicked is 1 in each of those two months, then surface just the campaign names whose ranking position worsened (higher rank number) from June to October.

## Worked solution and explanation

### Why this problem exists in real interviews

Marketing reports often compare a campaign's standing across two reference months to surface which campaigns lost ground. Interviewers use this prompt to see whether you can scope to two specific year-months in one pass, rank within each month independently, and self-join a CTE to compare the same campaign across periods.

---

### Break down the requirements

#### Step 1: Scope to two months and count clicks per (campaign, month)

Filter WHERE strftime('%Y-%m', impression_time) IN ('2026-06', '2026-10') so only the two reference months survive. Then GROUP BY ad_campaign and the same strftime expression. Use COUNT(CASE WHEN clicked = 1 THEN 1 END) so unclicked impressions are excluded by NULL (CASE without ELSE returns NULL, COUNT ignores NULLs).

#### Step 2: Rank within each month independently

DENSE_RANK() OVER (PARTITION BY ym ORDER BY total_clicks DESC) gives each campaign a no-gaps rank within its month. PARTITION BY ym is critical: without it the rank is global across both months and the comparison is meaningless.

#### Step 3: Self-join the ranked CTE on ad_campaign and compare

INNER JOIN ranked d (June) to ranked j (October) on ad_campaign. The INNER JOIN guarantees a campaign must appear in both months. Filter j.rnk > d.rnk to keep only campaigns whose October rank is worse than their June rank. Larger rank means worse position, so > (not <) is correct.

---

### The solution

**Two CTEs and a self-join across months**

```sql
WITH monthly_clicks AS (
  SELECT ad_campaign, strftime('%Y-%m', impression_time) AS ym,
         COUNT(CASE WHEN clicked = 1 THEN 1 END) AS total_clicks
  FROM ad_impressions
  WHERE strftime('%Y-%m', impression_time) IN ('2026-06', '2026-10')
  GROUP BY ad_campaign, strftime('%Y-%m', impression_time)
),
ranked AS (
  SELECT ad_campaign, ym, DENSE_RANK() OVER (PARTITION BY ym ORDER BY total_clicks DESC) AS rnk
  FROM monthly_clicks
)
SELECT d.ad_campaign
FROM ranked d INNER JOIN ranked j ON d.ad_campaign = j.ad_campaign
WHERE d.ym = '2026-06' AND j.ym = '2026-10' AND j.rnk > d.rnk
```

> **Cost Analysis**
>
> ad_impressions is 350,000,000 rows. The strftime IN filter cuts that to roughly two months of data, but it is non-sargable so it is still a full scan unless impression_time is indexed or partitioned. The two-CTE pattern avoids re-aggregating: monthly_clicks computes counts once, ranked reuses them for the rank, and the self-join touches at most a few thousand campaigns.

> **Interviewers Watch For**
>
> They want PARTITION BY ym in the rank, INNER JOIN to enforce the both-months requirement, and the correct direction of the inequality (j.rnk > d.rnk means October is worse). Strong candidates also call out that COUNT(CASE WHEN clicked = 1 THEN 1 END) and SUM(clicked) are equivalent here because clicked is 0/1, but the CASE form generalizes if clicked ever becomes nullable.

> **Common Pitfall**
>
> Forgetting PARTITION BY ym ranks across both months together, so every June row is ranked higher than every October row (or vice versa) and the comparison reduces to whichever month had more clicks overall. Using LEFT JOIN instead of INNER JOIN lets campaigns appear in only one month, which the prompt explicitly disallows. Reversing the inequality (j.rnk < d.rnk) returns campaigns that improved instead of regressed.

---

## Common follow-up questions

- How would you also report by how many ranks each campaign dropped? _(Adds j.rnk - d.rnk AS rank_delta to the projection and ORDER BY rank_delta DESC. The candidate should note that DENSE_RANK keeps the delta meaningful because there are no gaps.)_
- What if the spec changed to compare adjacent months over a year instead of two fixed months? _(Forces LAG(rnk) OVER (PARTITION BY ad_campaign ORDER BY ym) so each row sees its previous month's rank. The two-CTE self-join no longer scales when months are dynamic.)_
- What if a campaign has zero clicks in October? _(It still has a row in monthly_clicks (because it had impressions) but with total_clicks = 0, so it gets the lowest rank. The candidate should distinguish 'campaign existed but flopped' from 'campaign was not run' (no impressions at all).)_

## Related

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