# Buyers Who Never Browsed

> They bought without ever loading a page.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The fraud team flagged a batch of purchases made by users who have never browsed the site. Find every transaction from 2026 where the buyer has no record in page_views at all. Return each user's username and the transaction total, smallest amount first.

## Worked solution and explanation

### What this is really asking

This is an anti-join wearing a fraud-investigation costume. The real question: can you return the transactions whose user_id appears nowhere in page_views, without letting that 500M-row table blow up your query? Anyone can wire the three tables together. What separates candidates is the anti-join form they reach for and where they put the year filter. Get the filter wrong and you scope the browse table by accident, quietly turning 'never browsed' into 'browsed in that window'.

> **Trick to Solving**
>
> Use NOT EXISTS (SELECT 1 FROM page_views pv WHERE pv.user_id = t.user_id). It returns true the instant a single matching row is found, so it never materializes the 62-views-per-user fan-out a LEFT JOIN would build. Keep the date filter on transaction_date, never on viewed_at: scoping the page_views side would convert the anti-join back into an inner join.

---

### 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: NOT EXISTS (SELECT 1 FROM page_views pv WHERE pv.user_id = t.user_id) keeps only buyers whose user_id never appears in page_views. One matching row is enough to drop the transaction.

---

### Why NOT EXISTS, not LEFT JOIN

**LEFT JOIN + IS NULL**

Builds the full transactions-to-page_views match set first (62x fan-out, billions of rows), then discards every matched row with IS NULL. Correct, but it does the explosion before the cleanup.

**NOT EXISTS**

Stops scanning page_views the moment one matching user_id turns up. No intermediate fan-out, just an index probe per transaction. Same result, a fraction of the work on a 500M-row table.

### The solution

**NOT EXISTS anti-join**

```sql
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, u.username;
```

> **Cost Analysis**
>
> page_views is the giant: 500M rows, ~62 views per user. A LEFT JOIN would assemble a multi-billion-row intermediate before IS NULL throws most of it away. NOT EXISTS short-circuits on the first matching page_views row per user, so with an index on page_views.user_id it does a cheap point lookup instead of a fan-out.

> **Interviewers Watch For**
>
> Where the year filter lands (transaction_date, not viewed_at) and whether you reach for NOT EXISTS over NOT IN, which silently returns nothing if any page_views.user_id is NULL.

> **Common Pitfall**
>
> Using NOT IN (SELECT user_id FROM page_views) fails silently if any row has a NULL user_id: NOT IN against a subquery that contains a NULL returns no rows at all.

---

## 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 filter must correlate both sides: tests understanding of windowed vs unwindowed anti-joins.)_
- Rewrite this using LEFT JOIN + IS NULL, and explain when you would prefer it. _(Tests whether they know the three anti-join forms and the fan-out trade-off that makes NOT EXISTS the better choice here.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/unmatched_credit_complaints)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.