Table: Customer
+-------------+---------+ | Column Name | Type | +-------------+---------+ | customer_id | int | | product_key | int | +-------------+---------+ This table may contain duplicates rows.customer_idis not NULL.product_key is a foreign key (reference column) toProducttable.
Table: Product
+-------------+---------+ | Column Name | Type | +-------------+---------+ | product_key | int | +-------------+---------+ product_key is the primary key (column with unique values) for this table.
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_id | product_key | +-------------+-------------+ | 1 | 5 | | 2 | 6 | | 3 | 5 | | 3 | 6 | | 1 | 6 | +-------------+-------------+ 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.
In this approach, we'll use SQL's GROUP BY and HAVING clause to find customers who bought all products. We will count the number of distinct product_keys for each customer_id and compare it to the total number of products.
This SQL query groups rows by customer_id in the Customer table, then counts distinct product_key values for each group. The HAVING clause checks if the count for each customer_id equals the total count of products. Only customer_ids that satisfy this condition appear in the results.
C++
Java
Python
C#
JavaScript
Time Complexity: O(N * M), where N is the number of customers and M is the number of products due to the join and counting.
Space Complexity: O(K), where K is the number of distinct customer_ids in the result.
In this approach, we'll manually replicate the SQL logic using hash maps or dictionaries in a programming language. We'll count the distinct products each customer has bought using a hashmap and verify if it matches the total number of products.
We use a defaultdict to track products bought by each customer, forming a set for each customer to store unique products. After forming these sets, we iterate over to check whose product count equals product_count, the total number of products. Those customer_ids are captured and returned.
JavaScript
Java
C#
C++
Time Complexity: O(N), processes each entry of customer_data.
Space Complexity: O(U), U being the summed size of sets in the dictionary.
| Approach | Complexity |
|---|---|
| Approach using SQL with Group By | Time Complexity: O(N * M), where N is the number of customers and M is the number of products due to the join and counting. |
| Approach Using Programming Language with Hashmap/Dictionary | Time Complexity: O(N), processes each entry of customer_data. |
Customers Who Bought All Products | Leetcode 1045 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 4,428 views views
Watch 9 more video solutions →Practice Customers Who Bought All Products with our built-in code editor and test cases.
Practice on FleetCode