Users Without Purchases

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
L3
Asked in
Fidelity

Problem

The growth team is sizing the conversion gap: how many registered users have never completed a purchase?

How many registered users have never made a single purchase

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
users_without_purchases
185

Worked solution and explanation

Why this problem exists in real interviews

This is a classic anti-join problem. Interviewers check whether you can efficiently find records in one table with no match in another, and whether you choose LEFT JOIN ... IS NULL, NOT EXISTS, or NOT IN.

Break down the requirements

Step 1: Start from users

The count should reflect registered users, so users is the base table.

Step 2: Anti-join to transactions

LEFT JOIN transactions ON users.user_id = transactions.user_id WHERE transaction_id IS NULL finds users with no purchase records.

Step 3: Count the result

COUNT(*) returns the total number of non-purchasing users.

The solution

Anti-join for conversion gap sizing
SELECT COUNT(*) AS users_without_purchases
FROM users u
LEFT JOIN transactions t ON u.user_id = t.user_id
WHERE t.transaction_id IS NULL

Common follow-up questions