Welcome to Subscribe On Youtube
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;