Customers Without Orders

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
L4

Problem

Our signup numbers keep climbing while revenue stays flat, and we suspect many of the accounts in users have never bought anything. How many users have no purchase at all in transactions?

Customers who have never ordered.

Tables

users

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

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

transactions

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

transactions: 10 of 200 rows
transaction_iduser_idproduct_idquantitytotal_amounttransaction_date
10671971001223.462026-02-02
11342941050336.932026-03-03
12013911099450.42026-04-04
12684881148563.872026-05-05
13355851197002026-06-06
14026821246290.812026-07-07
146977912953104.282026-08-08
153687613444117.752026-09-09
160397313935-452026-01-10
1670107014421144.692026-02-11

Expected output

First 1 of 1 rows
no_order_count
185

Worked solution and explanation

Take away the business story and this is an anti-join: all accounts in users minus the accounts that appear in transactions, then counted. The phrase 'no purchase at all' does all the work. Anyone can join the two tables. What separates candidates is keeping the side that did NOT match, and proving it did not match. Only a small share of users ever bought anything. So a join that keeps matches counts buyers, which is the exact opposite answer. A NULL test on the wrong column quietly adds purchases back in as if they were non-buyers.

`transactions.user_id` points back to `users.user_id`; most accounts have no row on the transactions side.
  • users: user_id int PK, username text, email text, signup_date date, account_status text, age_bucket text
  • transactions: transaction_id int PK, user_id int FK, product_id int, total_amount numeric, transaction_date date
  • transactions.user_id references users.user_id

The trap that eats the answer

An inner join of users to transactions emits one row per purchase, so the accounts that never bought are gone before anything gets counted. Use the inner join only to build the positive set (who did buy). Then bring every account back with a left join and keep the rows that set never matched. The IS NULL on the right side is what performs the subtraction.

Step 1: Build the set of buyers

Inner-join users to transactions on user_id and keep DISTINCT u.user_id. A buyer has a dozen or more purchases, and DISTINCT collapses them to one row per buyer, so the buyer set really is a set.

Step 2: Keep every account with a LEFT JOIN

Left-join users to buyers. Every account survives. An account that is absent from buyers comes back with b.user_id padded to NULL.

Step 3: Test the join key for NULL

WHERE b.user_id IS NULL keeps exactly the accounts with no purchase. Test the key you joined on: it is never NULL on a real match, so a NULL there can only mean there was no match.

Step 4: Count the survivors

COUNT(*) over what is left is the single number that was asked for. Each users row is one account, so no further dedup is needed.

Name the buyers, then subtract them from every account
WITH buyers AS (
    SELECT DISTINCT u.user_id
    FROM users u
    INNER JOIN transactions t ON u.user_id = t.user_id
)
SELECT COUNT(*) AS no_order_count
FROM users u
LEFT JOIN buyers b ON u.user_id = b.user_id
WHERE b.user_id IS NULL

The CTE builds the buyer set with an inner join; the outer LEFT JOIN plus IS NULL keeps only the accounts that set never matched.

Inner join only (wrong)
Counting users joined to transactions tallies the purchase rows of accounts that DID buy. No unmatched row is left to keep, because the join has already dropped them.
Buyer set, then subtract (right)
The CTE names the buyers, the LEFT JOIN brings every account back, and IS NULL keeps only the accounts the buyer set never matched. That count is the answer.

Common follow-up questions