Two Names, One Campaign

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

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, walking through the ad campaign names in alphabetical order.

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

Tables

ad_impressions

Columns: impression_id INTEGER, user_id INTEGER, ad_campaign TEXT, impression_time TEXT, clicked INTEGER, revenue REAL

push_notifs

Columns: notif_id INTEGER, user_id INTEGER, title TEXT, platform TEXT, status TEXT, sent_at TEXT, opened INTEGER, campaign TEXT

Expected output

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

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.

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;

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.)