Customers Who Bought Products A And B But Not C - Leetcode Solution

Problem Statement:

Given a table sales, find the customers who bought product A and product B but did not buy product C.

sales table schema:

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| customer_id | int   |
| sale_date   | date  |
+-------------+-------+

Note:

• The sale_id and customer_id fields are the primary keys for this table.
• The product_id column contains only A, B, or C.

Solution:

To solve the given problem, we need to perform a join operation on the sales table with itself and filter out the records according to the conditions in the problem. We can use subqueries to achieve this.

The first subquery s1 retrieves the customer_id who purchased the product A. The second subquery s2 retrieves the customer_id who purchased the product B. The main query joins these two subqueries on the customer_id field to get the customers who bought both A and B products. Finally, we need to add a NOT IN condition for the product C to get the customers who did not buy the product C.

SELECT DISTINCT s1.customer_id
FROM sales s1
JOIN sales s2
ON s1.customer_id = s2.customer_id
WHERE s1.product_id = 'A' 
AND s2.product_id = 'B'
AND s1.customer_id NOT IN (SELECT customer_id FROM sales WHERE product_id = 'C');

In this solution, we are using DISTINCT to remove duplicate customer_id values.

Time complexity: O(n^2) Space complexity: O(1)

Alternate Solution:

An alternate solution can be achieved by using a GROUP BY clause and HAVING clause to group the entries by customer_id which purchased A or B, and count the number of products they bought, then exclude customers who bought product C.

SELECT customer_id
FROM sales
WHERE product_id IN ('A', 'B')
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = 2 
AND customer_id NOT IN (SELECT customer_id FROM sales WHERE product_id = 'C');

In this solution, we are using the GROUP BY clause to group the data by customer_id, and the HAVING clause is used to filter groups based on the count of distinct values of product_id. The NOT IN clause is used to exclude the customers who purchased C products.

Time complexity: O(nlogn) Space complexity: O(1)

1