# The Holdouts > Subscribed. But never upgraded. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L5 ## Problem In our push-notification log, each message records the plan tier it targeted in the `platform` field. Find the unique users who were sent a `basic`-tier notification but never a `premium`-tier one. ## Worked solution and explanation ### What this problem really is --- ### Why a single WHERE filter fails **Naive: filter basic rows** WHERE platform = 'basic' returns every user who was ever sent a basic-tier notification. But a user who started on basic and later upgraded still carries those old basic rows, so they slip into the result. You end up answering 'who has a basic notification', not 'who never went premium'. **Correct: basic minus premium** You want the users in the basic set who are absent from the premium set. Compute the premium users once, then keep only basic users who are not among them. The exclusion is per user across the full history, not per row. ### Building the query #### Step 1: Collect every user who ever went premium The inner query SELECT user_id FROM push_notifs WHERE platform = 'premium' builds the exclusion set: every user with at least one premium-tier notification anywhere in their history. This is the group you must remove, no matter how many basic rows they also carry. #### Step 2: Keep basic users not in that set WHERE platform = 'basic' AND user_id NOT IN (...) keeps only users who have a basic notification and never appear among the premium users. NOT IN performs the per-user set difference: presence in the subquery, even once, drops the user entirely. #### Step 3: Collapse duplicates with DISTINCT A user can have many basic notifications, so the filter alone returns one row per notification. SELECT DISTINCT user_id collapses them to one row per user, which is exactly what a campaign list needs. --- ### The solution **Basic-tier users who never appear with a premium tier** ```sql SELECT DISTINCT user_id FROM push_notifs WHERE platform = 'basic' AND user_id NOT IN (SELECT user_id FROM push_notifs WHERE platform = 'premium') ``` > **NOT IN turns silent when the subquery has NULLs** > > If the premium subquery could return a NULL user_id, NOT IN evaluates to UNKNOWN for every row and the whole result silently goes empty. Here user_id is NOT NULL so it is safe, but the moment that guarantee disappears, switch to NOT EXISTS, which is NULL-safe and usually plans the same. > **One scan, not a join blow-up** > > Across ~100M rows this is two passes over push_notifs: one to gather the premium user set, one to scan the basic rows. An index on (platform, user_id) lets the engine seek straight to the basic and premium slices instead of scanning all 100M. Resist a self-join here: joining the table to itself on user_id fans out on the hot zipf users and explodes. > **What separates the senior answer** > > The tell is whether you exclude on the user's whole history rather than the current row. Strong candidates say out loud that a user who upgraded must be dropped even though they still have basic rows, then reach for NOT EXISTS or an anti-join and name the NULL trap unprompted. --- ## Common follow-up questions - If a user has both basic and premium notifications but the premium one has status = 'failed' and was never delivered, should they still be excluded? _(Tests whether 'upgraded' means the tier was targeted or the message was actually delivered, forcing a clarifying question about the real signal.)_ - Would you use NOT EXISTS, a LEFT JOIN with IS NULL, or NOT IN here, and why? _(Tests query-plan judgment and NULL-safety awareness of NOT IN.)_ - How would you extend this to basic users who also never received any 'trial' or 'enterprise' tier notification? _(Tests generalizing a single-value exclusion into a multi-tier set difference.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_holdouts) - [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.