# Left on Read > Every campaign fights for the tap. Find the ones the field is leaving behind. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: Amazon ## Problem Our push notification system records an `opened` flag on every notification along with the campaign that sent it. Across every campaign that has sent notifications, find the ones whose opens outnumber those of the average campaign. ## Tables ### 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 5 rows: | campaign | open_count | | --- | --- | | flash_sale | 8 | | onboard_v2 | 8 | | promo_summer | 7 | | reengagement | 6 | | winback_feb | 6 | ## Worked solution and explanation ### What this really is --- ### Break down the requirements #### Step 1: Tally opens with SUM, not a pre-filter An open is `opened = 1`; a 0 or a `NULL` is not. Count opens with `SUM(opened)`: it adds up the set flags and ignores the zeros and the blanks, so you get each campaign's open tally without ever writing `WHERE opened = 1`. That matters, because that filter would erase the campaigns that earned no opens, and those campaigns still belong in the field you are measuring against. #### Step 2: Drop the phantom no-campaign group Filter `campaign IS NOT NULL` before you group, in the standings and again in the average. A notification with no campaign is not a campaign, and its rows are the single largest bucket in the table. Leave them in and they both win the standings outright and inflate the opens the average is built from, poisoning the very bar you are comparing everyone against. #### Step 3: Build the yardstick at the campaign grain The average campaign is total opens divided by the number of campaigns: `SUM(opened) * 1.0 / COUNT(DISTINCT campaign)`. The trick is the denominator. `COUNT(DISTINCT campaign)` counts every campaign that sent anything, including the ones that earned zero opens, so those quiet campaigns still weigh in the average even though they add nothing to the numerator. Keep the campaigns whose tally clears that bar. --- ### The solution **Keep the campaigns whose opens clear the average campaign** ```sql SELECT campaign, SUM(opened) AS open_count FROM push_notifs WHERE campaign IS NOT NULL GROUP BY campaign HAVING SUM(opened) > ( SELECT SUM(opened) * 1.0 / COUNT(DISTINCT campaign) FROM push_notifs WHERE campaign IS NOT NULL ) ``` > **The denominator is campaigns, not notifications** > > The bar is opens per campaign, so its denominator is `COUNT(DISTINCT campaign)`, not `COUNT(*)`. That is what makes every campaign weigh the same regardless of how many notifications it sent, and it is what keeps a zero-open campaign in the field: it counts toward the number of campaigns even though its opens are nothing. > **WHERE opened = 1 evicts the zero-open campaigns** > > `WHERE opened = 1` feels like the natural way to say 'only opens', but it deletes any campaign whose opens are all zero, and a deleted campaign cannot be counted in `COUNT(DISTINCT campaign)`. On this data that lifts the bar from about 2.4 to 3.4 and hands back two campaigns instead of five. Counting with `SUM(opened)` keeps the quiet campaigns in the table where they belong. **AVG over rows (wrong)** `AVG(opened)` across every notification yields the per-notification open rate near 0 to 1. Every campaign's open tally dwarfs it, so an above-the-bar filter keeps everyone and the grain is simply wrong. **average over campaigns (right)** `SUM(opened) * 1.0 / COUNT(DISTINCT campaign)` yields the opens the typical campaign earned. Keeping the campaigns whose tally clears that bar surfaces the genuine over-performers. > **What the interviewer is watching** > > They want to see you notice two grains at once: the average is over campaigns, not notifications, and a campaign with zero opens is still a campaign that counts in the denominator. Candidates who scope both, and who strip the null campaign from both the standings and the average, are signaling that they think in grains rather than in row filters. > **Two passes, both cheap** > > Both the subquery and the outer query scan `push_notifs`, but a covering index on (campaign, opened) turns each into an index-only aggregate scan, and the planner collapses 100M rows to at most 100 campaign groups before the average is taken. The double read rides a narrow index, not the 19GB heap. ## Common follow-up questions - A couple of blockbuster campaigns drag the mean upward, hiding some real over-performers below it. Would you switch to a median as the threshold, and how would you compute one in SQL? _(Tests understanding that the mean is sensitive to skew and awareness of percentile approaches.)_ - How would you also report each campaign's open rate (opens over sends) alongside its open count? _(Tests conditional counting: opens over sends per campaign.)_ - What happens to your threshold if a campaign sends notifications but records no opens, and where does it belong in the calculation? _(Tests whether the candidate realizes zero-open campaigns must remain in the denominator of the average.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/left_on_read) - [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.