Amazon SQL interview Questions – Leetcode – 183 – Customers Who Never Order

Problem Description –

Suppose that a website contains two tables, the Customers table, and the Orders table. Write a SQL query to find all customers who never order anything.

Table – Customers

Table – Orders

Using the above tables as examples, return the following.

Difficulty Level – Easy

Solution –

SELECT Name Customers
FROM Customers
WHERE Id NOT IN (SELECT CustomerId 
                 FROM Orders
                WHERE CustomerId IS NOT NULL)

In the subquery, we are selecting all the customer’s IDs who had made a purchase as the orders table will contain the IDs of customers who have made a purchase. Then in the outer query, we are using WHERE Id NOT IN to exclude all the IDs from the subquery. This will give us all the customers ids who have never made a purchase.

One important thing to notice in this query is the use of WHERE CustomerId IS NOT NULL in the subquery. If you want you can exclude this where clause and your answer will be accepted in Leetcode. But You should include this condition because if for some reason, the CustomerId column in the second table contains NULL values, then the query will return nothing.

Here, I have included a NULL value in the second table.

SELECT *
FROM Orders

You can see that there is a NULL value in the CustomersId column. And now, if I don’t include the Where clause condition in the subquery then look what we get.

SELECT Name Customers
FROM Customers
WHERE Id NOT IN (SELECT CustomerId 
                 FROM Orders)

As I said before, you will get an empty result.