Sellers With No Sales - Practice Coding Problems

Sellers With No Sales - Problem

Database Easy

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

Understanding the Visualization

Input Tables

Customer, Orders, and Seller tables

Join/Filter

Find sellers without 2020 sales

Output

Sellers with no 2020 activity

Key Takeaway

🎯 Key Insight: Use LEFT JOIN or NOT EXISTS to find missing relationships between tables

Asked in

A Amazon 15 M Microsoft 12 🍎 Apple 8

Use LEFT JOIN to connect all sellers with their 2020 orders, then filter for sellers with NULL matches (no sales), or use NOT EXISTS subquery to check for absence of 2020 sales.

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

Common Approaches

Approach	Time	Space	Notes
✓ NOT EXISTS Subquery	O(n * m)	O(1)	Use NOT EXISTS to find sellers without any 2020 sales
LEFT JOIN with NULL Check	O(n + m)	O(1)	LEFT JOIN sellers with 2020 orders and filter for NULL matches

NOT EXISTS Subquery — Algorithm Steps

Step 1: Select all sellers from Seller table
Step 2: Use NOT EXISTS with correlated subquery to check for 2020 orders
Step 3: Order results by seller_name

Visualization

Tap to expand

Step-by-Step Walkthrough

Check

For each seller, look for 2020 orders

Filter

Keep sellers with no matches

Sort

Order by name

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n * m)

For each seller, check all orders

✓ Linear Growth

Space Complexity

O(1)

Constant extra space

✓ Linear Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

NOT EXISTS Subquery — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler