SQL Quest › SQL Interview Questions › Subqueries & CTEs
Customers Without Orders
The win-back team needs a list of customers who signed up but never placed a single order — focused on the tiers worth re-engaging. Filter to membership IN ('Gold', 'Silver') and exclude anyone with at least one row in the orders table.
Show customer_id, name, membership. Order by membership ascending, then name ascending.
Use NOT EXISTS. The classic "why not NOT IN" answer: NOT IN is unsafe when the subquery returns any NULL — the comparison silently drops to UNKNOWN and the outer row is excluded even when it shouldn't be. NOT EXISTS sidesteps the NULL trap entirely (it's a row-existence check, not a value comparison) and it's also the form most query planners optimize best.
(Casing matters: the data stores membership as 'Gold'/'Silver'/'Bronze'/'Platinum' with a capital first letter — string equality is case-sensitive, so 'gold' would match nothing.)
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".
Schema
customers
| customer_id | name | signup_date | membership | total_orders | |
|---|---|---|---|---|---|
| 1 | John Smith | john.smith@email.com | 2023-01-15 | Gold | 15 |
| 2 | Emma Wilson | emma.wilson@email.com | 2023-03-20 | Silver | 8 |
| 3 | Michael Brown | michael.brown@email.com | 2023-02-10 | Gold | 12 |
orders
| order_id | customer_id | product | category | quantity | price | total | order_date | country | status |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | Laptop Pro | Electronics | 1 | 1299.99 | 1299.99 | 2024-01-15 | USA | completed |
| 2 | 2 | Wireless Mouse | Electronics | 2 | 49.99 | 99.98 | 2024-01-16 | Canada | completed |
| 3 | 3 | Office Chair | Furniture | 1 | 349.99 | 349.99 | 2024-01-17 | USA | completed |
Expected output: Nina appears, John does not
Hint
SELECT 1 inside NOT EXISTS is convention — the planner doesn't care what you select, only whether at least one row exists. Match the data's casing exactly: capital G and capital S.Concepts
SELECT Subquery NOT EXISTS
Practise the topic: SQL practice questions · CTE practice
In these company practice sets
A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test