# Two Names, One Campaign

> The ad team and the push team never agreed on naming. Find where they secretly meant the same thing.

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

Domain: SQL · Difficulty: easy · Seniority: L4

## Problem

Our advertising team labels campaigns in loud uppercase (ad_impressions.ad_campaign, e.g. FLASH_SALE_48H) while the push-notification team writes lowercase slugs (push_notifs.campaign, e.g. flash_sale), and the two naming systems were never reconciled. Some are plainly the same effort under a different name because they share a theme word: 'flash', 'loyalty', or 'summer'. Pair every ad campaign with the push campaign it secretly matches on one of those theme words.

## Worked solution and explanation

### What you're actually being asked

Strip the marketing costume and this is a fuzzy join across a keyword whitelist. Two systems named the same campaign differently, and the only bridge between them is a shared theme word. Nobody hands you FLASH_SALE_48H = flash_sale as a key; you manufacture the join predicate yourself out of LOWER plus LIKE. Anyone can type JOIN. What separates candidates is where they put the LOWER and how they group the keyword conditions.

### The trap: precedence and one-sided casing

> **The classic half-match**
>
> The two most common wrong answers: joining on ai.ad_campaign = pn.campaign, which matches zero rows because the strings never equal; and lowering only one side. LOWER(ai.ad_campaign) LIKE '%flash%' is true for FLASH_SALE_48H, but if you compare it against a raw pn.campaign you get lucky on the already-lowercase push side, then get burned the day someone uploads Flash_Sale. Lower BOTH sides, every time.

> **Watch the OR-of-ANDs**
>
> Each theme word needs its own AND pair, and the pairs are OR'd together. Flatten it into 'LIKE %flash% OR LIKE %flash% OR LIKE %loyalty% ...' and precedence quietly turns it into a cross join of nonsense: a summer ad matches a flash push. Interviewers read the parenthesization first. Correct grouping is the tell that you know AND binds tighter than OR and chose to be explicit anyway.

#### Step 1: Normalize both sides

Wrap both campaign columns in LOWER so casing stops mattering. Case is the whole reason the two teams' data never lined up, so fix it at the comparison, not by rewriting the source.

#### Step 2: Build one predicate per theme word

For each keyword, require it to appear on the ad side AND the push side. That AND is what makes it a genuine match rather than 'either side happens to mention flash'.

#### Step 3: Dedupe the pairs

ad_impressions carries many rows per campaign, so the raw join emits the same (ad, push) pair once per impression. DISTINCT collapses them back to one row per real match.

**Matched campaign pairs**

```sql
SELECT DISTINCT
       ai.ad_campaign,
       pn.campaign AS push_campaign
FROM   ad_impressions ai
JOIN   push_notifs pn
  ON  (LOWER(ai.ad_campaign) LIKE '%flash%'   AND LOWER(pn.campaign) LIKE '%flash%')
   OR (LOWER(ai.ad_campaign) LIKE '%loyalty%' AND LOWER(pn.campaign) LIKE '%loyalty%')
   OR (LOWER(ai.ad_campaign) LIKE '%summer%'  AND LOWER(pn.campaign) LIKE '%summer%')
ORDER BY ai.ad_campaign;
```

*One AND-pair per theme word, OR'd together, both sides lowered, deduped.*

> **Nulls fall out for free**
>
> push_notifs.campaign is null on several rows. You do not need an IS NOT NULL guard: LOWER(NULL) is NULL, NULL LIKE '%flash%' is NULL, and an inner join keeps only rows where the predicate is true. The null slugs simply never produce a match.

**Naive: equality join**

JOIN push_notifs pn ON ai.ad_campaign = pn.campaign returns zero rows. The uppercase names and the lowercase slugs are never equal strings.

**Correct: keyword bridge**

Match on a shared lowered keyword with LOWER(...) LIKE '%kw%' on both sides, OR-ing one AND-pair per theme word, then DISTINCT.

## Common follow-up questions

- The theme keyword list is hard-coded. How would you scale this to hundreds of themes without editing the query for each new one? _(Tests whether they reach for a keyword lookup table joined in, rather than an ever-growing OR chain.)_
- Two different ad campaigns both contain 'sale'. How do you keep them from being treated as the same effort? _(Probes the awareness that substring matching over-matches and needs a curated keyword set or token boundaries.)_
- How would you also surface ad campaigns that have NO push counterpart at all? _(Pushes them from an inner join to a left join with a null-side filter.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/two_names_one_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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.