Loyalty's Double Tap

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

L3

Asked in

Meta

Problem

Count the impressions each ad_campaign in ad_impressions served to customers who received a loyalty push, broken down by the platform that push went out on, as impression_count. For the growth team, a loyalty push is any push_notifs row whose campaign has 'loyalty' in its name, and the log's platform spellings such as 'Android' and 'android' are one platform, not two.

When a nudge and a banner team up.

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 this is really asking

Underneath the growth framing this is a membership-filtered overlap count with a dirty dimension: for each display-ad campaign and each push platform, how many impressions landed on customers a loyalty push targeted. Membership and platform live only in push_notifs, so the inner join on user_id carries them in. Anyone writes the join. Two things separate candidates. The log spells the same platform 'Android' and 'android', so grouping on the raw platform hands back one platform as two half-sized rows. And once the spellings are folded, a customer pushed under both spellings repeats every impression in that group, so a bare COUNT(*) inflates it. Both mistakes run clean.

Break down the requirements

Step 1: Define a loyalty push

A row in push_notifs qualifies when campaign LIKE '%loyalty%'. Delivery outcome and the opened flag are not part of the definition: being targeted is the membership.

Step 2: Keep only impressions for those customers

Inner join ad_impressions to the loyalty pushes on user_id. Impressions with a null user_id, and customers no loyalty push targeted, fall out of the join, which is exactly the population you want gone.

Step 3: Fold platform before it becomes a key

Use LOWER(pn.platform) in both the SELECT and the GROUP BY. If only one of them folds, the engine either rejects the query or still splits the groups; the key you group on and the key you show have to be the same expression.

Step 4: Count each impression once

Use COUNT(DISTINCT ai.impression_id). The join emits one row per impression per matching push, so the impression id is the only honest unit to count.

The solution

SELECT ai.ad_campaign,
       LOWER(pn.platform) AS platform,
       COUNT(DISTINCT ai.impression_id) AS impression_count
FROM ad_impressions ai
INNER JOIN push_notifs pn ON ai.user_id = pn.user_id
WHERE pn.campaign LIKE '%loyalty%'
GROUP BY ai.ad_campaign, LOWER(pn.platform)

Platform grouped as written

GROUP BY ad_campaign, platform treats 'Android' and 'android' as two platforms. Each half looks small, and anyone summing by platform downstream has to clean it again.

Platform folded first

GROUP BY ad_campaign, LOWER(platform) puts both spellings in one group, so each platform reads as the single number it is.

Common follow-up questions

• How would the count change if a customer had three loyalty pushes on the same platform, and which form of `COUNT` reflects the intended metric? (Tests that counting each `impression_id` once removes the join fan-out.)
• How would you also report how many different customers each `ad_campaign` reached per platform? (Adds a second count over `user_id` alongside `impression_count`.)
• What breaks if a campaign named 'disloyalty survey' appears in `push_notifs`? (Tests the risk of a loose substring match and how to anchor it.)