Question

1398. Customers Who Bought Products A and B but Not C

中文文档

Description

Table: Customers

+---------------------+---------+
| 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.

Return the result table ordered by customer_id.

The result format is in the following example.

 

Example 1:

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.

Solutions

Solution 1: LEFT JOIN + GROUP BY + HAVING

We can use LEFT JOIN to join the Customers table and the Orders table, then group them by customer_id, and finally filter out the customers who have purchased products A and B but not product C.

# Write your MySQL query statement below
SELECT customer_id, customer_name
FROM
  Customers
  LEFT JOIN Orders USING (customer_id)
GROUP BY 1
HAVING SUM(product_name = 'A') > 0 AND SUM(product_name = 'B') > 0 AND SUM(product_name = 'C') = 0
ORDER BY 1;