# The Notification That Paid Off

> The message went out to thousands. A smaller number actually bit.

Canonical URL: <https://datadriven.io/problems/the_notification_that_paid_off>

Domain: SQL · Difficulty: hard · Seniority: L5 · Asked in: ActiveCampaign

## Problem

Each row of `transactions` is one purchase, and whatever a user bought on their first day of purchasing is their starting basket. Report how many users bought a product outside their own starting basket within a year of that first day, and how many such purchases they made. A purchase with no product recorded is never new, and one on a user's first day leaves their basket unknown, so that user cannot qualify.

## Tables

### transactions

- transaction_id INTEGER
- user_id INTEGER
- product_id INTEGER
- quantity INTEGER
- total_amount REAL
- transaction_date TEXT

Sample rows (10 of 200):

| transaction_id | user_id | product_id | quantity | total_amount | transaction_date |
| --- | --- | --- | --- | --- | --- |
| 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 |
| 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 |
| 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 |
| 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 |
| 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 |
| 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 |
| 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 |
| 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 |
| 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 |
| 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 |

## Expected output

First 1 of 1 rows:

| converted_users | new_purchases |
| --- | --- |
| 15 | 93 |

## Worked solution and explanation

### What this really is

This is a **per-user set difference** dressed up as campaign attribution: each user's first year of purchases, minus that same user's day-one basket, counted twice, once as people and once as purchases. Anyone can find the first day with `MIN(transaction_date)`. What separates candidates is scoping the exclusion to the same `user_id`, closing the window, and keeping people apart from purchases. Get any of them wrong and every user converts on their own day-one shopping, or on a purchase made three years later, or a user with three new products is reported as three converts.

#### Step 1: Anchor each user to their first day

`MIN(transaction_date)` per `user_id` in `first_txn`. Every other date is measured from here, so compute it once and join back to it.

#### Step 2: Collect the starting basket

Join `transactions` to `first_txn` on both `user_id` and `transaction_date = first_date`. A user can buy several things that day; all of them are already owned.

#### Step 3: Close the window

Keep purchases where `julianday(t.transaction_date) <= julianday(ft.first_date) + 365`. There is no lower bound on purpose: first-day rows pass the date test, and the basket exclusion is what removes them.

#### Step 4: Subtract this user's basket

The correlated `NOT IN (... WHERE sb.user_id = t.user_id)` drops anything this user already owned. Its NULL behavior is doing real work too, covered below.

#### Step 5: Count people and purchases separately

`COUNT(DISTINCT t.user_id)` is the converted audience; `COUNT(*)` is every new purchase they made. Over a year most converted users buy more than once, so the two numbers genuinely differ: one user with four new purchases is one person and four purchases.

**Per-user set difference inside a one-year window**

```sql
WITH first_txn AS (
  SELECT user_id, MIN(transaction_date) AS first_date
  FROM transactions
  GROUP BY user_id
),
starting_basket AS (
  SELECT t.user_id, t.product_id
  FROM transactions t
  INNER JOIN first_txn ft
    ON t.user_id = ft.user_id
   AND t.transaction_date = ft.first_date
)
SELECT
  COUNT(DISTINCT t.user_id) AS converted_users,
  COUNT(*) AS new_purchases
FROM transactions t
INNER JOIN first_txn ft
  ON t.user_id = ft.user_id
WHERE julianday(t.transaction_date) <= julianday(ft.first_date) + 365
  AND t.product_id NOT IN (
    SELECT sb.product_id
    FROM starting_basket sb
    WHERE sb.user_id = t.user_id
  );
```

> **A global basket deletes real conversions**
>
> Dropping `WHERE sb.user_id = t.user_id` turns the basket into every product anyone bought on day one. A user who buys a lamp on day 12 vanishes because some other user bought a lamp on their first day.

> **Unknown is not the same as empty**
>
> If a user's basket holds a NULL, `x NOT IN (..., NULL)` is unknown for every `x`, so that user never qualifies: we cannot say what they already owned. A purchase whose own `product_id` is NULL is unknown too, so it is never new. Both rules in the statement fall out of three-valued logic with no extra predicate.

**NOT IN**

`t.product_id NOT IN (...)` returns unknown for a NULL purchase and for any user whose basket holds a NULL. Both match the statement exactly.

**NOT EXISTS / anti-join**

`NOT EXISTS (... AND sb.product_id = t.product_id)` keeps NULL-product purchases and ignores a NULL in the basket. Matching the statement needs `t.product_id IS NOT NULL` plus a separate exclusion of users whose basket holds a NULL.

> **The exclusion does the lower bound for free**
>
> Adding `> first_date` is harmless but redundant: every first-day purchase is in the basket by definition, so `NOT IN` already removes it. Remove the exclusion instead and every user converts on day one.

> **Name the window edges out loud**
>
> Ask whether day 365 is inclusive and whether 'first day' means a calendar day or a first timestamp. Raising both before writing SQL is the senior signal; silently picking one is not.

> **One pass to anchor, then hash joins**
>
> At 200M rows the `first_txn` aggregate scans every partition and dominates. Materialize it once; the basket join and the window filter hash-join on `user_id`, and the correlated `NOT IN` plans as an anti-join keyed on `(user_id, product_id)`.

## Common follow-up questions

- Rewrite the exclusion with `LEFT JOIN ... IS NULL`. What must you add so NULLs behave the same? _(Tests whether the candidate knows set-difference idioms differ on NULL semantics.)_
- Return the conversion rate: converted users over all users. _(Forces keeping the denominator before the filter removes users.)_
- How would the answer change if 'new' meant never bought by anyone before this user's first day? _(Checks that the per-user correlation was a deliberate choice.)_
- Late-arriving rows backfill older partitions. Does `first_txn` stay correct? _(`MIN(transaction_date)` is not stable under backfill.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_notification_that_paid_off)
- [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.