Watch the video solution for Sellers With No Sales, a easy level problem involving Database. This walkthrough by Everyday Data Science has 1,571 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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 | varchar | +---------------+---------+ seller_id is the column with unique values for this table. Each row of this table contains the information of each seller.
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.
The result format is in the following example.
Example 1:
Input: Customer table: +--------------+---------------+ | customer_id | customer_name | +--------------+---------------+ | 101 | Alice | | 102 | Bob | | 103 | Charlie | +--------------+---------------+ Orders table: +-------------+------------+--------------+-------------+-------------+ | order_id | sale_date | order_cost | customer_id | seller_id | +-------------+------------+--------------+-------------+-------------+ | 1 | 2020-03-01 | 1500 | 101 | 1 | | 2 | 2020-05-25 | 2400 | 102 | 2 | | 3 | 2019-05-25 | 800 | 101 | 3 | | 4 | 2020-09-13 | 1000 | 103 | 2 | | 5 | 2019-02-11 | 700 | 101 | 2 | +-------------+------------+--------------+-------------+-------------+ Seller table: +-------------+-------------+ | seller_id | seller_name | +-------------+-------------+ | 1 | Daniel | | 2 | Elizabeth | | 3 | Frank | +-------------+-------------+ Output: +-------------+ | seller_name | +-------------+ | Frank | +-------------+ Explanation: Daniel made 1 sale in March 2020. Elizabeth made 2 sales in 2020 and 1 sale in 2019. Frank made 1 sale in 2019 but no sales in 2020.
Problem Overview: You have two tables: Seller and Orders. Each order records the seller and the sale date. The task is to return the names of sellers who did not make any sales during the year 2020.
Approach 1: LEFT JOIN + GROUP BY + FILTER (O(n + m) time, O(1) extra space)
This approach joins every seller with their orders using a LEFT JOIN. A left join keeps all rows from the Seller table even when no matching order exists. After joining, group the results by seller and count only the orders that fall in the 2020 date range. Sellers whose count is zero are exactly the ones with no sales during that year.
The key insight: LEFT JOIN preserves sellers without matching rows, and conditional aggregation lets you filter by year while still keeping the join intact. Using GROUP BY seller_id plus a HAVING condition ensures you only return sellers whose filtered order count equals zero. This pattern appears frequently in SQL interview problems involving missing relationships.
This technique is common when solving problems involving SQL joins and GROUP BY aggregation. It performs well because the database scans both tables once and aggregates results efficiently.
Approach 2: NOT EXISTS Subquery (O(n + m) time, O(1) extra space)
Another clean solution uses a correlated subquery with NOT EXISTS. For each seller, check whether an order exists where the seller matches and the sale date falls in 2020. If such a row exists, that seller is excluded. If the subquery returns no rows, the seller qualifies.
The insight here is that NOT EXISTS is optimized by most SQL engines and stops searching as soon as a match is found. This often performs similarly to a join-based solution but can be easier to read because it expresses the condition directly: “no orders in 2020.”
Both approaches rely on core database query patterns: joining relational tables and filtering records based on time conditions.
Recommended for interviews: The LEFT JOIN + GROUP BY approach is the one most interviewers expect. It demonstrates that you understand join semantics and conditional aggregation. Mentioning the NOT EXISTS alternative shows deeper SQL knowledge and awareness of query optimization patterns.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| LEFT JOIN + GROUP BY + HAVING | O(n + m) | O(1) | Most common SQL interview pattern for detecting missing related rows |
| NOT EXISTS Subquery | O(n + m) | O(1) | Cleaner logic when expressing "no matching records" conditions |
| LEFT JOIN + WHERE IS NULL | O(n + m) | O(1) | Useful when filtering rows with completely missing matches instead of conditional aggregation |
