Problem

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   | text    |
+---------------+---------+
seller_id is the column with unique values for this table.
Each row of this table contains details about the seller.

Problem Definition

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.

Example

Image scaled to 80%

Output

Image scaled to 25%

Try It Yourself

Database Exercise

Database Schema:

-- Database schema would be rendered here

Exercise Script:

-- Exercise script would be rendered here

Available actions: Execute

Solution

To solve this problem, the solution involves using SQL queries to identify sellers who did not make any sales in the year 2020. The information is spread across the Customer and Orders tables, with the Orders table containing details about sales transactions, including sale dates, order costs, customer IDs, and seller IDs.

The solution combines the LEFT JOIN, ON, WHERE, and ORDER BY clauses. The LEFT JOIN is employed to join the Seller table (S) with the Orders table (O) based on the condition that the seller IDs match. The ON clause further refines the join by including only those sales that occurred in the year 2020, determined using the Year function on the sale_date column.

The WHERE clause filters the results only to include rows where there is no corresponding seller ID in the Orders table, effectively isolating sellers who did not make any sales in 2020. This is achieved by checking for a NULL value in the O.seller_id column.

The final step involves ordering the result table by seller_name in ascending order using the ORDER BY clause.

SELECT S.seller_name
FROM   Seller AS S
       LEFT JOIN Orders AS O
              ON S.seller_id = O.seller_id
                 AND Year(sale_date) = '2020'
WHERE  O.seller_id IS NULL
ORDER  BY seller_name 

Let’s break down the query step by step:

Step 1: Joining Seller and Orders tables

We need to connect the Seller and Orders tables based on the seller_id to get information about each sale and the associated seller.

SELECT S.seller_name
FROM Seller AS S
LEFT JOIN Orders AS O ON S.seller_id = O.seller_id

Output After Step 1:

+-------------+
| seller_name |
+-------------+
| Daniel      |
| Elizabeth   |
| Elizabeth   |
| Elizabeth   |
| Frank       |
+-------------+

Step 2: Filtering sales in 2020

Now, we want to include only the sales made in the year 2020.

AND YEAR(sale_date) = '2020'

Output After Step 2:

+-------------+
| seller_name |
+-------------+
| Daniel      |
| Elizabeth   |
| Elizabeth   |
| Frank       |
+-------------+

Step 3: Filtering sellers with no sales in 2020

To find sellers who did not make any sales in 2020, we use the WHERE clause to filter out those who had sales (O.seller_id IS NULL).

WHERE O.seller_id IS NULL

Output After Step 3:

+-------------+
| seller_name |
+-------------+
| Frank       |
+-------------+

Step 4: Ordering the result

Finally, we order the result by seller_name in ascending order.

ORDER BY seller_name

Final Output:

+-------------+
| seller_name |
+-------------+
| Frank       |
+-------------+