+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| customer_id | int |
| customer_name | varchar |
+---------------------+---------+
customer_id is the column with unique values for this table.
customer_name is the name of the customer.
Table: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| customer_id | int |
| product_name | varchar |
+---------------+---------+
order_id is the column with unique values for this table.
customer_id is the id of the customer who bought the product "product_name".
Write a solution to report the customer_id and customer_name of customers who bought products " A", " B" but did not buy the product " C" since we want to recommend them to purchase this product.
Input:
Customers table:+-------------+---------------+| customer_id | customer_name |+-------------+---------------+|1| Daniel ||2| Diana ||3| Elizabeth ||4| Jhon |+-------------+---------------+Orders table:+------------+--------------+---------------+| order_id | customer_id | product_name |+------------+--------------+---------------+|10|1| A ||20|1| B ||30|1| D ||40|1| C ||50|2| A ||60|3| A ||70|3| B ||80|3| D ||90|4| C |+------------+--------------+---------------+Output:
+-------------+---------------+| customer_id | customer_name |+-------------+---------------+|3| Elizabeth |+-------------+---------------+Explanation: Only the customer_id with id 3 bought the product A and B but not the product C.
The key idea is to group orders by customer and use conditional aggregation to check if a customer bought both products “A” and “B” but not “C”. This can be done using SUM and CASE statements in SQL, or boolean logic in pandas.