# Loyalty's Double Tap > When a nudge and a banner team up. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 · Asked in: Meta ## Problem Growth wants to see which of our display-ad campaigns are reaching customers who also sit in a loyalty push. A customer is in a loyalty push when `push_notifs` shows them a `campaign` whose name contains 'loyalty', in any casing. For each `ad_campaign` in `ad_impressions`, count the impressions served to those customers and return it as `impression_count`, most first. ## 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 | impression_count | | --- | --- | | LOYALTY_PROGRAM | 6 | | FLASH_SALE_48H | 4 | | BRAND_AWARENESS_Q1 | 3 | | RETARGETING_CART | 2 | | SUMMER_SALE_2024 | 1 | ## Worked solution and explanation ### What this is really asking Underneath the growth-metrics framing this is a per-campaign overlap count: for each display-ad campaign, how many of its impressions landed on customers who also sit in a loyalty push. That loyalty membership lives only in `push_notifs.campaign`, so you cannot answer it from the impressions table alone; the inner join on `user_id` is what carries it in. Two things bite. The campaign name is stored in whatever casing marketing happened to type, so an exact or case-sensitive match quietly drops most loyalty rows and your overlap number comes back far too small. And a customer can be reached by several loyalty pushes, so a bare COUNT(*) over the join counts the same impression once per push and inflates the number. Both mistakes still run clean, and nobody notices. --- ### Break down the requirements #### Step 1: Identify the loyalty push audience Scan `push_notifs` for loyalty campaigns with `LOWER(campaign) LIKE '%loyalty%'`, which catches every name that contains 'loyalty' in any casing. The `user_id` on those rows is the set of customers a loyalty push reached. #### Step 2: Keep only impressions for those customers Inner join `ad_impressions` to those `push_notifs` rows on `user_id`. The inner join drops impressions with a null or unmatched `user_id`, leaving only impressions served to loyalty customers. #### Step 3: Count distinct impressions per ad campaign Group by `ad_campaign` and use `COUNT(DISTINCT impression_id)`. The DISTINCT is what defuses the join fan-out: a customer reached by two loyalty pushes appears twice in the join, but the impression is still counted once. Order most impressions first, with `ad_campaign` as a tiebreak so the ordering is total. --- ### The solution **Inner join, case-insensitive filter, distinct count per campaign** ```sql SELECT ai.ad_campaign, 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 LOWER(pn.campaign) LIKE '%loyalty%' GROUP BY ai.ad_campaign ORDER BY impression_count DESC, ai.ad_campaign ``` > **Cost Analysis** > > The join is keyed on `user_id`; with an index on `push_notifs(user_id, campaign)` each probe is cheap. The join fans out for customers with several loyalty pushes, so COUNT(DISTINCT impression_id) collapses those duplicates back down before aggregating per ad campaign. > **Interviewers Watch For** > > The fan-out. A plain COUNT(*) over this join is a per-(impression, push) count, not a per-impression count, so a customer in two loyalty campaigns doubles their impressions. The question asks how many impressions each ad campaign served to loyalty customers, so an impression must be counted once. A strong candidate reaches for COUNT(DISTINCT impression_id) and says out loud why the bare COUNT(*) would over-report. > **Common Pitfall** > > Dropping `LOWER()` (or not reaching for `ILIKE`) makes the match case-sensitive, so campaigns stored as 'Loyalty' or 'LOYALTY' never match `LIKE '%loyalty%'` and the count comes back too low. Normalize the case before the substring match. --- ## Common follow-up questions - How does your count change if a customer received three separate loyalty pushes, and which version of COUNT reflects the intended metric? _(Tests understanding that COUNT(DISTINCT) removes the join fan-out while a bare COUNT(*) does not.)_ - How would you also report how many distinct loyalty customers each ad campaign reached? _(Requires a second grouping key or a distinct-user count alongside the impression count.)_ - What are the risks of the '%loyalty%' substring match, and how would you tighten it if 'loyalty' appeared inside an unrelated campaign name? _(Tests ILIKE or LOWER() for case-insensitive matching and the risk of matching an unintended substring.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/clicked_holiday_impressions) - [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.