Buyers Who Never Browsed

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Fidelity

Problem

Some 2026 purchases were made by buyers who never once appear in page_views, and the fraud team's review needs every one of those transactions. List each with the buyer's username and the transaction total, smallest amount first.

They bought without ever loading a page.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

transactions

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

page_views

Columns: view_id INTEGER, user_id INTEGER, viewed_at TEXT, page_url TEXT, referrer TEXT, dur_ms INTEGER, device TEXT

Expected output

Worked solution and explanation

What this is really asking

This is a set difference wearing a fraud-investigation costume: the year's purchases minus every buyer who shows up in page_views. Absence is the whole problem, and you cannot see absence by joining to the rows that exist. The candidate who inner-joins page_views returns precisely the browsers the review wanted gone, each one repeated about 62 times. The one who scopes page_views to the year through viewed_at quietly turns 'never browsed' into 'did not browse this year' and lets real browsers in. Any honest anti-join works; the trap is everything that is not one.

Break down the requirements

Step 1: Scope transactions to the target year

Filter the driving table to the target year: strftime('%Y', t.transaction_date) = '2026'. The year lives on transaction_date, so the page_views side stays completely unrestricted.

Step 2: Join to users for the username

Inner-join users on user_id to expose each buyer's username for the output. Every transaction references a real user, so this join neither adds nor drops rows.

Step 3: Exclude anyone with a page view

Apply the anti-join: the correlated NOT EXISTS keeps only buyers whose user_id never appears in page_views. One matching row is enough to drop the transaction, and nothing from page_views reaches the output.

Step 4: Order smallest purchase first

Sort by total_amount, smallest first. The only equal totals here are one buyer's repeat purchases at the same price, and those rows are identical, so a second sort key would change nothing.

Two anti-joins, one inverse

Inner join to `page_views`

JOIN page_views pv ON pv.user_id = t.user_id keeps only buyers who browsed: the inverse set, fanned out by every view. No WHERE clause downstream can recover the buyers it already dropped.

Anti-join

NOT EXISTS (or LEFT JOIN plus pv.user_id IS NULL) keeps the purchases with no partner. Both are correct; NOT EXISTS says it directly and stops at the first match.

The solution

SELECT u.username, t.total_amount
FROM transactions t
JOIN users u ON t.user_id = u.user_id
WHERE strftime('%Y', t.transaction_date) = '2026'
  AND NOT EXISTS (
        SELECT 1 FROM page_views pv WHERE pv.user_id = t.user_id
      )
ORDER BY t.total_amount;

Common follow-up questions

• How would you find users who browsed but never bought? (Flips the driving table: `page_views` becomes the side you keep and `transactions` the side you negate.)
• What if you want transactions with no page view in the same calendar month? (Now the correlation must match both the user and the month: tests windowed vs unwindowed anti-joins.)
• Rewrite this using `LEFT JOIN` + `IS NULL`, and explain when you would prefer it. (Tests whether they know the anti-join forms and the fan-out trade-off between them.)