Customers Who Bought All Products - Leetcode Solution

Customers Who Bought All Products is a problem on LeetCode that requires finding the customers who have bought all the products in a given list of orders.

The problem statement is as follows:

You are given a table, orders, which contains information about orders placed by customers. The table has the following columns:

• order_id: The id of the order.
• customer_id: The id of the customer who placed the order.
• product_name: The name of the product that was ordered.

Write a SQL query to find the customers who have bought all the products in the orders table.

Solution:

To solve this problem, we need to join the orders table with itself on the customer_id column and then group by the customer_id, product_name. After that, we need to count the number of unique products bought by each customer. If the count is equal to the total number of unique products available, then that customer has bought all the products.

Here's the SQL query to solve this problem:

SELECT customer_id FROM (
  SELECT customer_id, COUNT(DISTINCT product_name) AS num_products
  FROM orders
  GROUP BY customer_id
) AS t
WHERE num_products = (
  SELECT COUNT(DISTINCT product_name) FROM orders
);

Explanation:

• The inner query groups the orders table by customer_id and counts the number of unique products bought by each customer. This result is stored in a temporary table called t.
• The outer query filters the temporary table to only include customers who have bought all the products. It does this by comparing the number of unique products bought by each customer (num_products) to the total number of unique products available in the orders table (which is obtained through a subquery). If the counts match, then the customer_id is selected as the answer.

This query should produce the correct output for the Customers Who Bought All Products problem on LeetCode.

1