Table: Customers
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ id is the primary key (column with unique values) for this table. Each row of this table indicates the ID and name of a customer.
Table: Orders
+-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | customerId | int | +-------------+------+ id is the primary key (column with unique values) for this table. customerId is a foreign key (reference columns) of the ID from the Customers table. Each row of this table indicates the ID of an order and the ID of the customer who ordered it.
Write a solution to find all customers who never order anything.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Customers table: +----+-------+ | id | name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+ Orders table: +----+------------+ | id | customerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+ Output: +-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
The goal of #183 Customers Who Never Order is to identify customers in the Customers table who have never placed an order in the Orders table. Since the data is split across two related tables, the key idea is to compare records using the customer identifier.
A common approach is to use a LEFT JOIN between Customers and Orders. This keeps all customers in the result while matching orders when they exist. Customers whose joined order fields are NULL represent those who never placed an order. Another clean method is using NOT EXISTS or a NOT IN subquery to filter customers whose IDs never appear in the orders table.
Both strategies rely on relational comparison rather than iteration. In most database systems, these queries run efficiently with proper indexing on customer IDs. The time complexity is typically proportional to the size of the two tables.
| Approach | Time Complexity | Space Complexity |
|---|---|---|
| LEFT JOIN with NULL filter | O(n + m) | O(1) |
| NOT EXISTS / NOT IN Subquery | O(n + m) | O(1) |
Frederik Müller
The goal is to find customers who never made an order. By using a LEFT JOIN between the Customers and Orders tables, we can identify which customer IDs do not appear in the Orders table. We will check for NULL entries in the Orders table after the join to find these customers.
Time Complexity: O(n), where n is the number of rows in the Customers table.
Space Complexity: O(n), primarily for storing the result set.
1import pandas as pd
2
3# Sample data
4customers_data = {'id': [1, 2, 3, 4], 'name': ['Joe', 'Henry', 'Sam', 'Max']}
5orders_data = {'id': [1, 2], 'customerId': [3, 1]}
6
7# Creating DataFrame
8df_customers = pd.DataFrame(customers_data)
9df_orders = pd.DataFrame(orders_data)
10
11# Performing the left join
12result = pd.merge(df_customers, df_orders, left_on='id', right_on='customerId', how='left')
13
14# Selecting customers who never ordered
15never_ordered = result[result['customerId'].isnull()]['name'].tolist()
16print(never_ordered) # Output: ['Henry', 'Max']This solution uses Python's pandas library to replicate the SQL LEFT JOIN operation. We first create DataFrames for the customers and orders data. Then, a LEFT JOIN is performed where the left DataFrame is df_customers and we match it based on 'id' with 'customerId' of df_orders. After merging, we filter the entries where 'customerId' is NaN (indicating no matching order), and collect the corresponding customer names.
This approach uses a subquery to first identify the list of customers who have placed orders. Then, it excludes these customers from the list of all customers to find those who haven't placed any orders.
Time Complexity: O(n + m), with n as the number of customers and m as the number of orders.
Space Complexity: O(1) for the operation itself, but the result set storage would be O(k), where k is the number of customers who have never ordered.
1customers = {1: 'Joe', 2: 'Henry', 3: 'Sam', 4: 'Max'}
2orders = {1: 3, 2: 1}
3
4customers_set = set(customers.keys())
orders_set = set(orders.values())
never_ordered = [customers[cust_id] for cust_id in (customers_set - orders_set)]
print(never_ordered) # Output: ['Henry', 'Max']Watch expert explanations and walkthroughs
Practice problems asked by these companies to ace your technical interviews.
Explore More ProblemsJot down your thoughts, approach, and key learnings
Yes, variations of this problem appear in technical interviews, especially for roles involving SQL or data analysis. It tests a candidate's understanding of joins, filtering, and relational database thinking.
This problem primarily tests SQL joins and subqueries. Developers often use LEFT JOIN with a NULL check or a NOT EXISTS clause to detect customers who do not appear in the Orders table.
The most common approach is using a LEFT JOIN between the Customers and Orders tables and filtering rows where the order record is NULL. This ensures all customers are included while identifying those without matching orders. It is simple, readable, and efficient in most SQL databases.
The key concept is relational table comparison using a shared key, typically customer ID. Understanding joins, foreign keys, and how NULL values behave in SQL queries is important for solving this problem efficiently.
This Python solution uses basic set operations. It initializes two sets: one with all customer IDs and another with IDs that have placed orders. By taking the difference of these sets, we identify customer IDs that never appear in orders. We then map these IDs back to their corresponding names.