Sellers With No Sales - Practice Coding Problems

Sellers With No Sales - Problem

Database Easy

Find Sellers With No Sales

You're analyzing a webstore's sales data to identify underperforming sellers. Given three database tables - Customer, Orders, and Seller - your task is to find all sellers who didn't make any sales during the year 2020.

Tables:
• Customer: Contains customer information
• Orders: Contains all order transactions with sale dates
• Seller: Contains seller information

Goal: Write a SQL query to return the names of all sellers who had zero sales in 2020, ordered alphabetically by seller name.

This is a classic SQL problem that tests your understanding of JOINs, date filtering, and finding missing relationships between tables.

Input & Output

example_1.sql — Basic Case

$ Input: Customer: | customer_id | customer_name | |-------------|---------------| | 1 | Alice | | 2 | Bob | Orders: | order_id | sale_date | customer_id | seller_id | |----------|------------|-------------|-----------| | 1 | 2020-03-01 | 1 | 1 | | 2 | 2020-05-25 | 2 | 3 | | 3 | 2021-02-15 | 1 | 2 | Seller: | seller_id | seller_name | |-----------|-------------| | 1 | Alice | | 2 | Bob | | 3 | Carol |

› Output: | seller_name | |-------------| | Bob |

💡 Note: Bob (seller_id=2) only has a sale in 2021, not in 2020. Alice and Carol both have sales in 2020, so only Bob is returned.

example_2.sql — Multiple No-Sales Sellers

$ Input: Customer: | customer_id | customer_name | |-------------|---------------| | 1 | John | Orders: | order_id | sale_date | customer_id | seller_id | |----------|------------|-------------|-----------| | 1 | 2020-01-01 | 1 | 1 | Seller: | seller_id | seller_name | |-----------|-------------| | 1 | Alice | | 2 | Bob | | 3 | Carol | | 4 | Dave |

› Output: | seller_name | |-------------| | Bob | | Carol | | Dave |

💡 Note: Only Alice (seller_id=1) made a sale in 2020. Bob, Carol, and Dave made no sales, so they are returned in alphabetical order.

example_3.sql — All Sellers Have Sales

$ Input: Customer: | customer_id | customer_name | |-------------|---------------| | 1 | John | | 2 | Jane | Orders: | order_id | sale_date | customer_id | seller_id | |----------|------------|-------------|-----------| | 1 | 2020-01-01 | 1 | 1 | | 2 | 2020-06-15 | 2 | 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 are returned (empty result set).

Visualization

Tap to expand

Understanding the Visualization

Identify the Tables

We need Seller (all salespeople) and Orders (2020 transactions only)

LEFT JOIN Strategy

Connect every seller with their 2020 orders, preserving sellers with no orders

Filter for Missing

Find sellers where the join produced NULL (no 2020 sales)

Sort Results

Order alphabetically by seller name

Key Takeaway

🎯 Key Insight: LEFT JOIN preserves all records from the left table (Seller), making it perfect for finding missing relationships. The WHERE IS NULL filter identifies sellers without any 2020 sales.

Time & Space Complexity

Time Complexity

⏱️

O(n×m)

n sellers × m orders to check each seller against all orders

✓ Linear Growth

Space Complexity

O(1)

No additional space needed beyond result set

✓ Linear Space

Constraints

1 ≤ Number of customers ≤ 10⁴
1 ≤ Number of orders ≤ 10⁵
1 ≤ Number of sellers ≤ 10³
sale_date is in YYYY-MM-DD format
All seller_id values in Orders table exist in Seller table
Results must be ordered by seller_name in ascending order

Asked in

a Amazon 45 ⊞ Microsoft 38 f Meta 32 G Google 28

The optimal solution uses a LEFT JOIN between the Seller and Orders tables, filtering for 2020 sales in the JOIN condition. We then use WHERE o.seller_id IS NULL to find sellers with no matching orders. This approach is efficient and leverages SQL's built-in JOIN optimization.

Common Approaches

Approach	Time	Space	Notes
✓ Nested Subquery Approach	O(n×m)	O(1)	Check each seller individually with EXISTS subquery
LEFT JOIN Approach	O(n + m)	O(n)	Join all sellers with their 2020 orders and filter for nulls

Nested Subquery Approach — Algorithm Steps

Select all sellers from the Seller table
For each seller, use NOT EXISTS with a subquery
Subquery checks if seller_id appears in Orders with 2020 date
Return sellers where subquery returns no results
Order results by seller_name

Visualization

Tap to expand

Step-by-Step Walkthrough

Start with Sellers

Begin with all sellers in the Seller table

Check Each Seller

For each seller, run subquery to find 2020 orders

Filter Results

Keep only sellers with no matching orders

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n×m)

n sellers × m orders to check each seller against all orders

✓ Linear Growth

Space Complexity

O(1)

No additional space needed beyond result set

✓ Linear Space

Constraints

1 ≤ Number of customers ≤ 10⁴
1 ≤ Number of orders ≤ 10⁵
1 ≤ Number of sellers ≤ 10³
sale_date is in YYYY-MM-DD format
All seller_id values in Orders table exist in Seller table
Results must be ordered by seller_name in ascending order

52.3K Views

High Frequency

~15 min Avg. Time

1.8K 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Nested Subquery Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler