Question

1607. Sellers With No Sales

中文文档

Description

Table: Customer

+---------------+---------+
| Column Name   | Type  |
+---------------+---------+
| customer_id   | int   |
| customer_name | varchar |
+---------------+---------+
customer_id is the column with unique values for this table.
Each row of this table contains the information of each customer in the WebStore.

 

Table: Orders

+---------------+---------+
| Column Name   | Type  |
+---------------+---------+
| order_id    | int   |
| sale_date   | date  |
| order_cost  | int   |
| customer_id   | int   |
| seller_id   | int   |
+---------------+---------+
order_id is the column with unique values for this table.
Each row of this table contains all orders made in the webstore.
sale_date is the date when the transaction was made between the customer (customer_id) and the seller (seller_id).

 

Table: Seller

+---------------+---------+
| Column Name   | Type  |
+---------------+---------+
| seller_id   | int   |
| seller_name   | varchar |
+---------------+---------+
seller_id is the column with unique values for this table.
Each row of this table contains the information of each seller.

 

Write a solution to report the names of all sellers who did not make any sales in 2020.

Return the result table ordered by seller_name in ascending order.

The result format is in the following example.

 

Example 1:

Input: 
Customer table:
+--------------+---------------+
| customer_id  | customer_name |
+--------------+---------------+
| 101      | Alice     |
| 102      | Bob       |
| 103      | Charlie     |
+--------------+---------------+
Orders table:
+-------------+------------+--------------+-------------+-------------+
| order_id  | sale_date  | order_cost   | customer_id | seller_id   |
+-------------+------------+--------------+-------------+-------------+
| 1       | 2020-03-01 | 1500     | 101     | 1       |
| 2       | 2020-05-25 | 2400     | 102     | 2       |
| 3       | 2019-05-25 | 800      | 101     | 3       |
| 4       | 2020-09-13 | 1000     | 103     | 2       |
| 5       | 2019-02-11 | 700      | 101     | 2       |
+-------------+------------+--------------+-------------+-------------+
Seller table:
+-------------+-------------+
| seller_id   | seller_name |
+-------------+-------------+
| 1       | Daniel    |
| 2       | Elizabeth   |
| 3       | Frank     |
+-------------+-------------+
Output: 
+-------------+
| seller_name |
+-------------+
| Frank     |
+-------------+
Explanation: 
Daniel made 1 sale in March 2020.
Elizabeth made 2 sales in 2020 and 1 sale in 2019.
Frank made 1 sale in 2019 but no sales in 2020.

Solutions

Solution 1: LEFT JOIN + GROUP BY + FILTER

We can use a left join to join the Seller table with the Orders table on the condition seller_id, and then group by seller_id to count the number of sales for each seller in the year $2020$. Finally, we can filter out the sellers with zero sales.

# Write your MySQL query statement below
SELECT seller_name
FROM
  Seller
  LEFT JOIN Orders USING (seller_id)
GROUP BY seller_id
HAVING IFNULL(SUM(YEAR(sale_date) = 2020), 0) = 0
ORDER BY 1;