SQL interview questions – Leetcode 1045 – Customers Who Bought All Products

Problem Description –

Write an SQL query for a report that provides the customer ids from the Customer table that bought all the products in the Product table.

Return the result table in any order.

The query result format is in the following example:

The customers who bought all the products (5 and 6) are customers with Ids 1 and 3.

Difficulty Level – Medium

Problem Link – https://leetcode.com/problems/customers-who-bought-all-products/

Solution –

SELECT
    customer_id
FROM customer
GROUP BY customer_id
HAVING COUNT( DISTINCT product_key) = (SELECT COUNT(*) FROM product)

In the product table, we have two unique products and the question asks us to select all the customers who bought all of the products from this table.

So, let’s first group the data by customer_id and count how many unique products each customer has bought.

SELECT 
	customer_id,
	COUNT( DISTINCT product_key) unique_product
FROM customer
GROUP BY customer_id

We can see that both customers 1 and 3 bought two unique products and we also know that there are two unique products in the product table. So, both customers 1 and 3 bought all the products but customer 2 has only bought a single product so, this customer will not be included in the final result.

Now, to write in a single query, we are just moving the COUNT function from the select clause to the having clause and using a subquery to get the product count from the product table using this query – HAVING COUNT( DISTINCT product_key) = (SELECT COUNT(*) FROM product)