Sellers With No Sales - Problem

Given three tables - Customer, Orders, and Seller - find all sellers who did not make any sales in the year 2020.

The Customer table contains customer information, the Orders table contains all sales transactions with dates, and the Seller table contains seller information.

Return the result ordered by seller_name in ascending order.

Table Schema

Customer
Column Name Type Description
customer_id PK int Primary key for customer
customer_name varchar Name of the customer
Primary Key: customer_id
Orders
Column Name Type Description
order_id PK int Primary key for order
sale_date date Date when transaction was made
order_cost int Cost of the order
customer_id int Foreign key to Customer table
seller_id int Foreign key to Seller table
Primary Key: order_id
Seller
Column Name Type Description
seller_id PK int Primary key for seller
seller_name varchar Name of the seller
Primary Key: seller_id

Input & Output

Example 1 — Mixed Sales Activity
Input Tables:
Customer
customer_id customer_name
1 Daniel
2 Elizabeth
Orders
order_id sale_date order_cost customer_id seller_id
1 2020-03-01 1500 1 1
2 2020-05-25 2400 2 3
Seller
seller_id seller_name
1 Alice
2 Bob
3 Charlie
Output:
seller_name
Bob
💡 Note:

Alice (seller_id=1) made a sale on 2020-03-01, and Charlie (seller_id=3) made a sale on 2020-05-25. Only Bob (seller_id=2) has no sales in 2020, so he appears in the result.
Example 2 — All Sellers Have Sales
Input Tables:
Customer
customer_id customer_name
1 John
Orders
order_id sale_date order_cost customer_id seller_id
1 2020-01-15 1000 1 1
2 2020-12-31 2000 1 2
Seller
seller_id seller_name
1 Alice
2 Bob
Output:
seller_name
💡 Note:

Both Alice and Bob made sales in 2020, so no sellers appear in the result (empty result set).
Example 3 — Sales Outside 2020
Input Tables:
Customer
customer_id customer_name
1 Sarah
Orders
order_id sale_date order_cost customer_id seller_id
1 2019-12-31 500 1 1
2 2021-01-01 800 1 2
Seller
seller_id seller_name
1 David
2 Emma
Output:
seller_name
David
Emma
💡 Note:

Both sellers have sales, but none occurred in 2020 (one in 2019, one in 2021). Since we only look for 2020 sales, both sellers are included in the result, ordered alphabetically.

Constraints

  • 1 ≤ seller_id, customer_id ≤ 100
  • 1 ≤ order_id ≤ 1000
  • seller_name and customer_name are non-empty strings
  • sale_date is a valid date
  • order_cost ≥ 0

Visualization

Tap to expand
Sellers With No Sales INPUT Seller Table seller_id | seller_name 1 | Daniel 2 | Elizabeth 3 | Frank 4 | Grace Orders Table order_id|seller_id|sale_date 1 | 1 | 2020-03-01 2 | 1 | 2020-05-15 3 | 2 | 2019-08-10 4 | 3 | 2019-12-20 Customer Table customer_id | name 1 | Alice 2 | Bob ALGORITHM STEPS 1 Find 2020 Sellers Get seller_ids with sales in year 2020 SELECT DISTINCT seller_id WHERE YEAR(sale_date)=2020 2 Subquery Result Returns: seller_id = 1 3 NOT IN Filter Exclude sellers found in 2020 sales WHERE seller_id NOT IN (SELECT from Orders WHERE YEAR = 2020) 4 Order Results ORDER BY seller_name ASC Sort alphabetically FINAL RESULT Filtering Process All Sellers Daniel Elizabeth Frank Grace 2020 Sales Daniel (ID:1) X Output seller_name Elizabeth Frank Grace OK - 3 sellers found Sorted alphabetically Key Insight: Use a subquery with NOT IN to find sellers who have no matching orders in the specified year. LEFT JOIN with NULL check is an alternative: SELECT s.* FROM Seller s LEFT JOIN Orders o ON s.seller_id = o.seller_id AND YEAR(o.sale_date) = 2020 WHERE o.order_id IS NULL ORDER BY s.seller_name TutorialsPoint - Sellers With No Sales | Optimal Solution

Related Problems

Asked in
Amazon 15 Microsoft 12 Apple 8
23.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen