LeetCode 1607 - Sellers With No Sales

This problem asks us to identify all sellers who did not make any sales during the year 2020. We are given three databas

leetcodeeasydatabase

LeetCode Problem 1607

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

This problem asks us to identify all sellers who did not make any sales during the year 2020.

We are given three database tables:

The Customer table contains information about customers in the web store. Each customer has a unique customer_id and a customer_name. However, this table is not actually necessary for solving the problem because the task only concerns sellers and orders.

The Orders table stores transaction information. Each row represents an order and contains details such as the sale date, the customer involved, and the seller responsible for the transaction. Most importantly, we are interested in the sale_date and seller_id columns because we need to determine which sellers made sales specifically in the year 2020.

The Seller table contains all sellers in the platform. Every seller has a unique seller_id and a seller_name.

The goal is to return the names of sellers who had zero sales in 2020, sorted alphabetically by seller_name in ascending order.

In other words, we must:

  1. Find all sellers who made at least one sale in 2020.
  2. Exclude those sellers from the complete seller list.
  3. Return the remaining seller names in ascending order.

Looking at the example:

  • Daniel sold something in March 2020.
  • Elizabeth sold multiple times in 2020.
  • Frank only sold in 2019.

Since Frank has no sales in 2020, he is included in the result.

Because this is a database problem, the input is represented as relational tables rather than arrays or objects. The output is a result table containing a single column, seller_name.

An important detail is that sales in other years should not matter. A seller who sold products in 2019 or 2021 but not in 2020 must still be included.

Several edge cases are worth considering. A seller may have no orders at all, in which case they definitely belong in the output. Multiple sales in 2020 should still count as just one reason to exclude the seller. Sellers with only non-2020 sales must remain in the answer. The problem guarantees unique IDs for sellers and orders, which simplifies joins and filtering.

Approaches

Brute Force Approach

A brute-force solution would examine every seller individually and scan the entire Orders table to determine whether that seller made any sale in 2020.

For each seller, we would iterate through all orders and check two conditions:

  • Whether the seller_id matches the current seller.
  • Whether the sale_date falls in the year 2020.

If we find even one matching order, we exclude the seller from the result. Otherwise, we include them.

This approach is correct because every seller is checked against every order, guaranteeing that no qualifying sale is missed. However, it is inefficient because it repeatedly scans the Orders table for every seller.

Optimal Approach

The key insight is that we do not need to repeatedly search through orders for each seller. Instead, we can first identify all sellers who had at least one sale in 2020, then remove them from the seller list.

In SQL, this naturally translates to using a subquery with NOT IN, or alternatively a LEFT JOIN with filtering.

The process becomes:

  1. Extract all seller_id values that appear in 2020 orders.
  2. Select sellers whose IDs are not in that set.
  3. Sort the results alphabetically.

This is more efficient because we process the orders table once to determine active sellers in 2020.

Approach Time Complexity Space Complexity Notes
Brute Force O(S × O) O(1) Scan all orders for every seller
Optimal O(S + O) O(O) Build a set of sellers with 2020 sales

Here, S represents the number of sellers and O represents the number of orders.

Algorithm Walkthrough

Optimal SQL Strategy

  1. Query the Orders table and find all seller_id values where the sale occurred in 2020. This creates a set of sellers who should be excluded.
  2. Query the Seller table and select sellers whose seller_id does not appear in the exclusion set. This guarantees we only keep sellers with zero sales in 2020.
  3. Sort the remaining sellers by seller_name in ascending order to satisfy the problem requirement.
  4. Return only the seller_name column.

Why it works

The correctness of this approach comes from a simple invariant: every seller belongs to exactly one of two groups, sellers who made at least one sale in 2020, and sellers who did not. By explicitly constructing the first group and excluding it from the full seller list, the remaining sellers must be precisely those with no 2020 sales.

Python Solution

Since this is a database problem, the Python section represents the SQL query exactly as submitted on LeetCode.

import pandas as pd

def sellers_with_no_sales(customer: pd.DataFrame,
                           orders: pd.DataFrame,
                           seller: pd.DataFrame) -> pd.DataFrame:
    sellers_2020 = orders[
        orders["sale_date"].dt.year == 2020
    ]["seller_id"].unique()

    result = seller[
        ~seller["seller_id"].isin(sellers_2020)
    ][["seller_name"]]

    return result.sort_values("seller_name")

The implementation first filters the Orders table to keep only rows where the sale year is 2020. From these rows, it extracts the unique seller_id values, which represent sellers who made sales during that year.

Next, the Seller table is filtered using isin(). Any seller whose ID appears in the 2020 seller set is excluded. Only the seller_name column is selected because that is the required output format.

Finally, the result is sorted alphabetically by seller_name before being returned.

For the actual LeetCode SQL submission, the solution is:

# SQL query representation
SELECT seller_name
FROM Seller
WHERE seller_id NOT IN (
    SELECT seller_id
    FROM Orders
    WHERE YEAR(sale_date) = 2020
)
ORDER BY seller_name;

Go Solution

Go is not applicable for LeetCode Database problems because submissions are written in SQL rather than a programming language. The equivalent SQL solution is shown below.

// Database problems on LeetCode are solved in SQL.
// Equivalent SQL:

SELECT seller_name
FROM Seller
WHERE seller_id NOT IN (
    SELECT seller_id
    FROM Orders
    WHERE YEAR(sale_date) = 2020
)
ORDER BY seller_name;

Unlike algorithmic problems where Go and Python implementations differ in memory management or data structures, database problems are language independent because execution happens inside the SQL engine.

Worked Examples

Example 1

Input Tables

Orders

order_id sale_date seller_id
1 2020-03-01 1
2 2020-05-25 2
3 2019-05-25 3
4 2020-09-13 2
5 2019-02-11 2

Seller

seller_id seller_name
1 Daniel
2 Elizabeth
3 Frank

Step 1: Find sellers with 2020 sales

We scan the Orders table and keep only rows from 2020.

order_id sale_date seller_id
1 2020-03-01 1
2 2020-05-25 2
4 2020-09-13 2

The set of sellers with 2020 sales becomes:

Seller IDs
{1, 2}

Step 2: Remove those sellers

We filter the Seller table.

seller_id seller_name In Exclusion Set?
1 Daniel Yes
2 Elizabeth Yes
3 Frank No

Only Frank remains.

Step 3: Sort alphabetically

The result contains only one seller:

seller_name
Frank

Complexity Analysis

Measure Complexity Explanation
Time O(S + O) Scan orders once and sellers once
Space O(O) Store seller IDs with 2020 sales

The time complexity is linear because we process the Orders table once to identify sellers with 2020 sales and process the Seller table once to filter results. The space complexity comes from storing seller IDs found in 2020 orders. In the worst case, every order belongs to a unique seller.

Test Cases

def sellers_with_no_sales(customer, orders, seller):
    sellers_2020 = {
        order["seller_id"]
        for order in orders
        if order["sale_date"].startswith("2020")
    }

    result = sorted([
        s["seller_name"]
        for s in seller
        if s["seller_id"] not in sellers_2020
    ])

    return result

# Example case from prompt
assert sellers_with_no_sales(
    [],
    [
        {"seller_id": 1, "sale_date": "2020-03-01"},
        {"seller_id": 2, "sale_date": "2020-05-25"},
        {"seller_id": 3, "sale_date": "2019-05-25"},
        {"seller_id": 2, "sale_date": "2020-09-13"},
        {"seller_id": 2, "sale_date": "2019-02-11"},
    ],
    [
        {"seller_id": 1, "seller_name": "Daniel"},
        {"seller_id": 2, "seller_name": "Elizabeth"},
        {"seller_id": 3, "seller_name": "Frank"},
    ]
) == ["Frank"]  # Provided example

# Seller with no orders at all
assert sellers_with_no_sales(
    [],
    [],
    [{"seller_id": 1, "seller_name": "Alice"}]
) == ["Alice"]  # No sales anywhere

# Sellers with only non-2020 sales
assert sellers_with_no_sales(
    [],
    [{"seller_id": 1, "sale_date": "2019-01-01"}],
    [{"seller_id": 1, "seller_name": "Bob"}]
) == ["Bob"]  # Old sales only

# Every seller has 2020 sales
assert sellers_with_no_sales(
    [],
    [
        {"seller_id": 1, "sale_date": "2020-01-01"},
        {"seller_id": 2, "sale_date": "2020-06-15"},
    ],
    [
        {"seller_id": 1, "seller_name": "Alice"},
        {"seller_id": 2, "seller_name": "Bob"},
    ]
) == []  # Nobody qualifies

# Multiple 2020 sales by same seller
assert sellers_with_no_sales(
    [],
    [
        {"seller_id": 1, "sale_date": "2020-01-01"},
        {"seller_id": 1, "sale_date": "2020-05-05"},
    ],
    [{"seller_id": 1, "seller_name": "Alice"}]
) == []  # Duplicate sales should not matter
Test Why
Provided example Validates baseline correctness
No orders Ensures sellers with zero sales are included
Only non-2020 sales Confirms year filtering works correctly
Every seller sold in 2020 Verifies empty result handling
Multiple 2020 sales Ensures duplicate sales do not affect correctness

Edge Cases

One important edge case occurs when a seller has no orders at all. A naive implementation might accidentally exclude such sellers if it only checks sellers present in the Orders table. Our implementation handles this correctly because it begins with the complete Seller table and excludes only sellers who explicitly appear in 2020 transactions.

Another important case is when a seller has sales only outside 2020, such as in 2019 or 2021. A careless implementation might incorrectly exclude them by checking whether the seller appears in any order. Our solution specifically filters for YEAR(sale_date) = 2020, ensuring only relevant transactions matter.

A third edge case involves multiple sales by the same seller in 2020. Without deduplication, an implementation might waste memory or perform redundant checks. Using DISTINCT behavior through a subquery or unique seller ID set ensures repeated sales do not affect correctness, since a seller either had at least one 2020 sale or did not.

Finally, consider the case where every seller made sales in 2020. The correct output should be an empty table. Since our query filters sellers using NOT IN, it naturally returns no rows when every seller belongs to the exclusion set.