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
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
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)