# Loyalty's Double Tap > When a nudge and a banner team up. Canonical URL: 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. ## Tables ### ad_impressions - impression_id INTEGER - user_id INTEGER - ad_campaign TEXT - impression_time TEXT - clicked INTEGER - revenue REAL Sample rows (10 of 200): | impression_id | user_id | ad_campaign | impression_time | clicked | revenue | | --- | --- | --- | --- | --- | --- | | 253 | 100 | BRAND_AWARENESS_Q1 | 2026-02-02 01:07:00 | 0 | NULL | | 306 | 197 | PRODUCT_LAUNCH_X | 2026-03-03 02:14:00 | 0 | NULL | | 359 | 294 | RETARGETING_CART | 2026-04-04 03:21:00 | 0 | NULL | | 412 | 391 | HOLIDAY_PROMO | 2026-05-05 04:28:00 | 0 | NULL | | 465 | 488 | NEW_USER_ACQU | 2026-06-06 05:35:00 | 1 | 0.2 | | 518 | NULL | LOYALTY_PROGRAM | 2026-07-07 06:42:00 | 0 | NULL | | 571 | 682 | FLASH_SALE_48H | 2026-08-08 07:49:00 | 0 | NULL | | 624 | 779 | SUMMER_SALE_2024 | 2026-09-09 08:56:00 | 0 | NULL | | 677 | 876 | BRAND_AWARENESS_Q1 | 2026-01-10 09:03:00 | 0 | NULL | | 730 | 973 | PRODUCT_LAUNCH_X | 2026-02-11 10:10:00 | 1 | 0.35 | ### push_notifs - notif_id INTEGER - user_id INTEGER - title TEXT - platform TEXT - status TEXT - sent_at TEXT - opened INTEGER - campaign TEXT Sample rows (10 of 200): | notif_id | user_id | title | platform | status | sent_at | opened | campaign | | --- | --- | --- | --- | --- | --- | --- | --- | | 1053 | 100 | New message from team | android | pending | 2026-02-02 01:07:00 | 0 | winback_feb | | 1106 | 197 | Weekly digest ready | web | failed | 2026-03-03 02:14:00 | 0 | promo_summer | | 1159 | 294 | Price drop alert | iOS | bounced | 2026-04-04 03:21:00 | 1 | onboard_v2 | | 1212 | 391 | Security alert | Android | Delivered | 2026-05-05 04:28:00 | 0 | NULL | | 1265 | 488 | Payment received | WEB | FAILED | 2026-06-06 05:35:00 | NULL | loyalty_2024 | | 1318 | 585 | Complete your profile | basic | delivered | 2026-07-07 06:42:00 | 1 | NULL | | 1371 | 682 | New feature available | premium | pending | 2026-08-08 07:49:00 | 0 | reengagement | | 1424 | 779 | Subscription renewal | ios | failed | 2026-09-09 08:56:00 | 0 | NULL | | 1477 | 876 | Flash sale: 50% off | android | bounced | 2026-01-10 09:03:00 | 1 | flash_sale | | 1530 | 973 | Your order shipped! | web | Delivered | 2026-02-11 10:10:00 | NULL | retention_q1 | ## Expected output First 5 of 6 rows: | ad_campaign | platform | impression_count | | --- | --- | --- | | BRAND_AWARENESS_Q1 | web | 3 | | FLASH_SALE_48H | web | 4 | | LOYALTY_PROGRAM | android | 6 | | NEW_USER_ACQU | web | 4 | | SUMMER_SALE_2024 | ios | 1 | ## 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 **Inner join, folded platform, count each impression once** ```sql 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. > **Folding the key creates the fan-out** > > The strong signal is noticing that cleaning `platform` merges pushes that used to sit in separate groups. A customer pushed once as 'Android' and once as 'android' now doubles every impression under `COUNT(*)`; reaching for `COUNT(DISTINCT impression_id)` and saying why reads as seniority. > **Profile every column you group on** > > Candidates check the campaign name and skip the dimension. Any text column that becomes a group key needs a quick look at its spellings; `platform` here carries several casings of the same value. > **Filter the push side before it joins** > > The `LIKE` touches only `push_notifs`, so the engine shrinks it to loyalty rows before probing `ad_impressions` on `user_id`. A leading wildcard cannot use a plain index; at scale, tag loyalty campaigns with a flag column instead. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/loyalty_s_double_tap) - [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.