The Holdouts

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

L5

Asked in

Infosys

Problem

Our basic-tier winback list keeps including people we already messaged during 2025, and they are supposed to sit this campaign out. From push_notifs, where platform records the plan tier a message targeted, find the users who were ever sent a basic-tier notification but received no notification of any kind in 2025.

Subscribed. But never upgraded.

Tables

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 problem really is

This is a per-user set difference wearing a winback costume: everyone in the basic-tier set, minus everyone who shows up anywhere in the 2025 slice of push_notifs. Anyone can write WHERE platform = 'basic'. The trap is testing the year on the same row as the tier. Every basic user in this log also has a 2026 send, so a row-level sent_at condition always finds one row that passes and keeps them. Get that wrong and the cooldown excludes nobody: the users we already reached in 2025 get the winback push anyway.

Why a row filter fails

Naive: filter the rows

Adding NOT (sent_at >= '2025-01-01' AND sent_at < '2026-01-01') to the basic filter throws away the 2025 rows but keeps each user's 2026 row, so all 12 basic users survive. It answers 'who has a basic send outside 2025', not 'who was left alone in 2025'.

Correct: exclude the user

Build the set of users with any 2025 send, then keep only basic users absent from it. The test runs against each user's whole history, so one 2025 row removes them no matter how many other rows they have.

Building the query

Step 1: Collect everyone contacted in 2025

The subquery SELECT user_id FROM push_notifs WHERE sent_at >= '2025-01-01' AND sent_at < '2026-01-01' is the cooldown set. It deliberately ignores platform: a device push or a premium push in 2025 still means we reached that person.

Step 2: Keep basic users outside that set

platform = 'basic' AND user_id NOT IN (...) keeps a row only when its user never appears in the cooldown set. Membership is decided per user, which is exactly what the row filter could not do.

Step 3: Collapse to one row per user

Each basic user has several sends in the log, so without DISTINCT the same user_id comes back once per surviving row. The list is an audience: one entry per person.

The solution

SELECT DISTINCT user_id
FROM push_notifs
WHERE platform = 'basic'
  AND user_id NOT IN (
    SELECT user_id
    FROM push_notifs
    WHERE sent_at >= '2025-01-01'
      AND sent_at < '2026-01-01'
  )

Common follow-up questions

• If only delivered sends should start a cooldown, how do you handle `status` values like 'Delivered' and 'delivered'? (Tests narrowing the exclusion set and normalizing a dirty status column.)
• Would you write this with `NOT EXISTS`, a `LEFT JOIN` with an `IS NULL` check, or `NOT IN`, and why? (Tests anti-join fluency and NULL-safety.)
• How would you turn the fixed 2025 window into a rolling 365-day cooldown measured from each campaign's send date? (Tests moving from a fixed window to a relative one.)