The Notification That Paid Off

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

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

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

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.

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
  );

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.

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.)