Description

Table: Customer

Column NameType
customer_idint
product_keyint
  • This table may contain duplicates rows.
  • customer_id is not NULL.
  • product_key is a foreign key (reference column) to Product table.

Table: Product

Column NameType
product_keyint
  • product_key is the primary key (column with unique values) for this table.

Problem Statement

Write a solution to report the customer ids from the Customer table that bought all the products in the Product table. Return the result table in any order. The result format is in the following example.

Example 1:

Input:

  • Customer table:
customer_idproduct_key
15
26
35
36
16

Product table:

product_key
5
6

Output:

customer_id
1
3

Explanation:

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

Solution

You need to verify that customer_id has bought all products from Product table. This you can verify by actually checking the product_key values from the Customer table, or by simply checking the COUNT(DISTINCT product_key) value from the Customer table. The solution uses second approach.

  1. Find the count of distinct product_key from Product table.
  2. For each customer in Customer table, find the count of distinct product_key. If this count matches the count from step 1, then the customer has bought all products from Product table.
1SELECT customer_id
2    FROM Customer
3    GROUP BY customer_id
4    HAVING COUNT(DISTINCT product_key) IN (
5        SELECT COUNT(DISTINCT product_key) FROM Product
6    );