This original exercise uses four fictional customers and four orders. It is a practice problem, not a claim about questions asked by a particular employer. Run the script in an empty SQLite database; the examples below were tested with Python’s built-in SQLite module.

Read the data before writing the query

Avery has two completed orders. Morgan has one canceled order. Taylor and Jordan have no orders. A fourth order has an unknown customer ID. That missing ID is deliberate: it exposes a common mistake in a NOT IN subquery.

The customer table has one row per customer; the orders table has one row per order. The requested result is one row per customer. An ordinary join can repeat Avery, but an existence check asks only whether at least one qualifying row is present.

Run three queries and compare the answers

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT);
INSERT INTO customers VALUES (1,'Avery'),(2,'Morgan'),(3,'Taylor'),(4,'Jordan');
INSERT INTO orders VALUES (101,1,'completed'),(102,1,'completed'),(103,2,'cancelled'),(104,NULL,'completed');

-- No orders of any status: customers 3 and 4.
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
 SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

-- No completed orders: customers 2, 3 and 4.
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
 ON o.customer_id = c.customer_id AND o.status = 'completed'
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

-- Deliberate trap: returns no rows because the subquery contains NULL.
SELECT c.customer_id
FROM customers c
WHERE c.customer_id NOT IN (SELECT customer_id FROM orders);

Explain the result, not just the syntax

The first query returns 3, Taylor and 4, Jordan. Morgan is excluded because a canceled order is still an order under the first definition. Avery is excluded regardless of having one or two matching orders.

The second query returns 2, Morgan; 3, Taylor; and 4, Jordan. It asks a different question: who has no completed orders? The completed-status predicate belongs in the join condition so the match search includes only qualifying orders. Testing the non-nullable order primary key for NULL then identifies customers without such a match.

The third query intentionally returns zero rows. Avery and Morgan match values in the subquery, so NOT IN is false. For Taylor and Jordan, the unknown value prevents the exclusion condition from becoming true. A WHERE clause keeps true conditions, not unknown ones. The PostgreSQL subquery reference documents this NULL behavior and the existence-check semantics.

Why moving the status filter breaks the left join

If you move o.status = ‘completed’ into WHERE alongside o.order_id IS NULL, an unmatched row has a NULL status and cannot satisfy the completed-status requirement. The query no longer expresses “no completed orders.” Define the qualifying match in ON, then check for its absence.

You can also use NOT EXISTS with the status predicate inside the subquery. Choose the form you can explain clearly. Neither form is universally faster: database engine, indexes, data distribution and the execution plan matter. This exercise tests correctness, not a performance ranking.

What if the interviewer means “no orders this month”?

Ask which date records the event, which time zone defines the month and which statuses count. Add the reporting window inside the qualifying-order condition. A customer with purchases last year but none this month should remain in the result if this month is the intended scope.

Use a clearly defined start boundary and exclusive end boundary for timestamps. Do not silently interpret a US business day using a UK reporting zone, or classify someone as inactive before the data for the period is complete. See the SQL reporting-boundary guide for date reasoning.

Validate the output before using it

• Check that the customer universe includes the population you intend to study, rather than only customers already present in orders.
• Verify the output has no duplicate customer IDs. If the customer source itself has duplicates, NOT EXISTS will not deduplicate it.
• Track the unknown customer ID separately as a data-quality issue. Do not assign that order to a customer by guesswork.
• Test both a customer with multiple qualifying orders and a customer with only nonqualifying orders.
• Reconcile customers with qualifying orders plus customers without them against the complete customer population.

In this dataset, two customers have any orders and two do not: four in total. Only one has completed orders and three do not: again four. Those partitions provide a simple independent check of the two definitions.

A concise interview answer

“I would clarify the qualifying order definition, start from the customer population, and use NOT EXISTS against that match condition. I would test canceled orders and missing customer IDs. I would avoid an unfiltered NOT IN when the subquery can contain NULL, and confirm that my result stays at customer grain.”

Continue with SQL join fundamentals, NULL handling and order/payment reconciliation. For coached practice, check preparation plans and current availability. All mock interviews and feedback are in English.