Sales Person solution and explanation
Google
9 more videos on this problem
LeetCode 607 Amazon Interview SQL Question with Detailed Explanation | Practice SQLEveryday Data Science · 6:44 · 7K views
LeetCode 607 - Sales Person - SQL SolutionNariman Alimuradov · 8:44 · 1K views
LeetCode 607 - Sales Person - Interview Coding RoundBig Tech Mindset · 3:55 · 816 views
LeetCode 607 - Sales Person (Python and SQL) [Easy]Ryan & Matt Data Science · 9:38 · 642 views
607. Sales Person leetcode solutionrealNameHidden · 8:42 · 625 views
LeetCode - 607. Sales Person (SQL)Chain Of Thought · 5:00 · 536 views
SQL Interview Questions |Leetcode SQL 607.Sales Person #leetcode #sqlinterviewquestionsMoody Mansi😉 · 9:50 · 385 views
607. Sales Person | LeetCode | SQL | Solution | Interview ProblemData Alchemist · 6:19 · 225 views
LeetCode 607 | Sales Person | SubQuery | Left Join | Inner Join | SQLSleepy Cracker · 3:46 · 221 views
607. Sales Person | SQL Query] | Leetcode problem EasyPravhakar Das · 5:47 · 220 views
The problem
Table: SalesPerson
+-----------------+---------+ | Column Name | Type | +-----------------+---------+ | sales_id | int | | name | varchar | | salary | int | | commission_rate | int | | hire_date | date | +-----------------+---------+ sales_id is the primary key (column with unique values) for this table. Each row of this table indicates the name and the ID of a salesperson alongside their salary, commission rate, and hire date.
Table: Company
+-------------+---------+ | Column Name | Type | +-------------+---------+ | com_id | int | | name | varchar | | city | varchar | +-------------+---------+ com_id is the primary key (column with unique values) for this table. Each row of this table indicates the name and the ID of a company and the city in which the company is located.
Table: Orders
+-------------+------+ | Column Name | Type | +-------------+------+ | order_id | int | | order_date | date | | com_id | int | | sales_id | int | | amount | int | +-------------+------+ order_id is the primary key (column with unique values) for this table. com_id is a foreign key (reference column) to com_id from the Company table. sales_id is a foreign key (reference column) to sales_id from the SalesPerson table. Each row of this table contains information about one order. This includes the ID of the company, the ID of the salesperson, the date of the order, and the amount paid.
Write a solution to find the names of all the salespersons who did not have any orders related to the company with the name "RED".
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: SalesPerson table: +----------+------+--------+-----------------+------------+ | sales_id | name | salary | commission_rate | hire_date | +----------+------+--------+-----------------+------------+ | 1 | John | 100000 | 6 | 4/1/2006 | | 2 | Amy | 12000 | 5 | 5/1/2010 | | 3 | Mark | 65000 | 12 | 12/25/2008 | | 4 | Pam | 25000 | 25 | 1/1/2005 | | 5 | Alex | 5000 | 10 | 2/3/2007 | +----------+------+--------+-----------------+------------+ Company table: +--------+--------+----------+ | com_id | name | city | +--------+--------+----------+ | 1 | RED | Boston | | 2 | ORANGE | New York | | 3 | YELLOW | Boston | | 4 | GREEN | Austin | +--------+--------+----------+ Orders table: +----------+------------+--------+----------+--------+ | order_id | order_date | com_id | sales_id | amount | +----------+------------+--------+----------+--------+ | 1 | 1/1/2014 | 3 | 4 | 10000 | | 2 | 2/1/2014 | 4 | 5 | 5000 | | 3 | 3/1/2014 | 1 | 1 | 50000 | | 4 | 4/1/2014 | 1 | 4 | 25000 | +----------+------------+--------+----------+--------+ Output: +------+ | name | +------+ | Amy | | Mark | | Alex | +------+ Explanation: According to orders 3 and 4 in the Orders table, it is easy to tell that only salesperson John and Pam have sales to company RED, so we report all the other names in the table salesperson.
Approach overview
Problem Overview: You are given three tables: salesperson, company, and orders. The task is to return the names of salespersons who never handled an order for the company named RED. The challenge is filtering salespeople based on the absence of a specific relationship across joined tables.
Approach 1: Using NOT EXISTS with Subquery (O(n) time, O(1) extra space)
This approach checks for the absence of a matching record using a correlated subquery. For each salesperson, a subquery searches the orders table joined with company to see if an order exists where the company name is RED. The NOT EXISTS predicate returns true only when no such record is found. The key insight is that databases can short‑circuit existence checks efficiently, making this pattern common in SQL interview problems. Use this when you want a clear “exclude if any match exists” condition.
Approach 2: Using LEFT JOIN and IS NULL (O(n) time, O(1) extra space)
This method performs a LEFT JOIN from salesperson to orders associated with the company RED. If a salesperson never handled a RED order, the joined columns from that filtered dataset remain NULL. Filtering rows with WHERE joined_column IS NULL removes anyone who actually matched RED. This pattern is widely used when solving absence queries using SQL joins. It is easy to read and often preferred when you already need joins for other conditions.
Approach 3: INNER JOIN with GROUP BY and HAVING (O(n) time, O(1) extra space)
This approach joins salesperson, orders, and company, then aggregates results by salesperson. A conditional aggregation counts how many orders are linked to the company RED. The HAVING clause keeps only those groups where the count of RED orders equals zero. This technique relies on grouping logic from database querying. It works well when the query already requires aggregation for other metrics.
Approach 4: LEFT JOIN with NULL Filtering After Company Match (O(n) time, O(1) extra space)
Another variation performs a join between orders and company first, restricting rows to RED. Then a LEFT JOIN connects that result to salesperson. Salespeople who never appear in the RED order set produce NULL values and are selected with a WHERE ... IS NULL filter. This separates the filtering step from the join logic and keeps the query modular.
Recommended for interviews: The NOT EXISTS solution is usually the cleanest and easiest to reason about. It directly expresses the requirement: return salespeople for whom no RED order exists. Showing the LEFT JOIN ... IS NULL alternative demonstrates deeper understanding of relational filtering patterns commonly used in SQL interview questions.
Approach 1: Approach 1: Using NOT EXISTS with Subquery
This approach utilizes a subquery within a NOT EXISTS condition to identify salespersons who have not placed any orders for the company named 'RED'. By leveraging the NOT EXISTS, we can efficiently filter out salespersons who are not involved with any orders corresponding to 'RED'.
SELECT name FROM SalesPerson WHERE NOT EXISTS (SELECT 1 FROM Orders o JOIN Company c ON o.com_id = c.com_id WHERE c.name = 'RED' AND o.sales_id = SalesPerson.sales_id)The above SQL query selects all salespersons from the SalesPerson table who do not exist in the subquery result. The subquery joins the Orders and Company tables, filtering for orders related to the company named 'RED'. The NOT EXISTS clause excludes salespersons who have orders related to 'RED'.
Time Complexity: O(N * M), where N is the number of salespersons and M is the number of orders.
Space Complexity: O(1), since we are using in-DB operations without additional data structures.
Approach 2: Approach 2: Using LEFT JOIN and IS NULL
This approach involves performing a LEFT JOIN operation between the SalesPerson and Orders tables, filtering orders related to 'RED', and then checking for NULLs to identify salespersons with no such orders. This exploits the fact that when a LEFT JOIN does not find matches, the result will have NULL entries.
SELECT s.name FROM SalesPerson s LEFT JOIN Orders o ON s.sales_id = o.sales_id LEFT JOIN Company c ON o.com_id = c.com_id AND c.name = 'RED' WHERE c.com_id IS NULLThis SQL query first performs a LEFT JOIN between the SalesPerson, Orders, and Company tables. With the condition filtering for company name 'RED', salespersons without related orders will result in NULL for the company columns. The WHERE clause checks for these NULL entries, effectively excluding salespersons with orders for 'RED'.
Time Complexity: O(N + M), where N is the number of salespersons and M is the number of companies/orders.
Space Complexity: O(1), as operations occur within the database without additional data structure utilization.
Approach 3: Approach 1: Use INNER JOIN and GROUP BY
This approach involves using INNER JOIN to identify the salespersons who have placed orders for the company named RED. Then, with a subquery and GROUP BY, we identify all salespersons with no association with RED by excluding them from the full list of salespersons.
SELECT name FROM SalesPerson WHERE sales_id NOT IN ( SELECT DISTINCT sales_id FROM Orders INNER JOIN Company ON Orders.com_id = Company.com_id WHERE name = 'RED' );This SQL query does the following:
- It retrieves all salesperson names from the SalesPerson table.
- Excludes those who have made orders for the company RED by using a subquery.
- The subquery joins the Orders and Company tables to find the sales_id of orders made to RED.
- The use of NOT IN excludes these salespeople from the final list.
Time Complexity: O(n + m + k) where n, m, and k are the number of rows in SalesPerson, Orders, and Company tables respectively.
Space Complexity: O(n).
Approach 4: Approach 2: Use LEFT JOIN and NULL Filtering
This approach exploits the LEFT JOIN feature to link salespersons with orders related to the company RED. We then filter out the entries where no such orders exist, effectively capturing the desired salespersons.
SELECT sp.name FROM SalesPerson sp LEFT JOIN Orders o ON sp.sales_id = o.sales_id LEFT JOIN Company c ON o.com_id = c.com_id AND c.name = 'RED' WHERE c.com_id IS NULL;The solution works as follows:
- Perform a LEFT JOIN between SalesPerson and Orders tables based on sales_id.
- Apply a second LEFT JOIN with the Company table filtered for the name RED.
- Finally, filter on c.com_id IS NULL to find salespersons without orders to the company RED.
Time Complexity: O(n * log m) where n and m are the number of rows in SalesPerson and Orders tables respectively.
Space Complexity: O(n).
Complexity comparison
| Approach | Complexity |
|---|---|
| Approach 1: Using NOT EXISTS with Subquery | Time Complexity: O(N * M), where N is the number of salespersons and M is the number of orders. |
| Approach 2: Using LEFT JOIN and IS NULL | Time Complexity: O(N + M), where N is the number of salespersons and M is the number of companies/orders. |
| Approach 1: Use INNER JOIN and GROUP BY | Time Complexity: O(n + m + k) where n, m, and k are the number of rows in SalesPerson, Orders, and Company tables respectively. |
| Approach 2: Use LEFT JOIN and NULL Filtering | Time Complexity: O(n * log m) where n and m are the number of rows in SalesPerson and Orders tables respectively. |
Complexity analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| NOT EXISTS with Subquery | O(n) | O(1) | Best when filtering rows that must not have a related record |
| LEFT JOIN + IS NULL | O(n) | O(1) | Common SQL pattern for detecting missing relationships |
| INNER JOIN with GROUP BY | O(n) | O(1) | Useful when aggregation or counts are already needed |
| LEFT JOIN with Filtered Dataset | O(n) | O(1) | Cleaner structure when isolating the RED company orders first |
Frequently asked questions
Is Sales Person easy or hard?
Sales Person is classified as an Easy database problem. The main concept is understanding how to filter rows when a related record does not exist, which is a common SQL interview pattern involving NOT EXISTS or LEFT JOIN with NULL checks.
Sales Person Python/Java solution
This problem is designed for SQL rather than Python or Java because it operates directly on relational tables. On coding platforms, the expected answer is a SQL query using joins or subqueries to filter salespeople who never handled orders for the company 'RED'.
How to solve Sales Person in O(n)?
Join the relevant tables and filter out salespeople associated with the company 'RED'. Using NOT EXISTS or LEFT JOIN with an IS NULL filter allows the database engine to scan orders and companies once while checking for matching relationships, resulting in linear-time behavior relative to table size.
What is the best approach for Sales Person?
The NOT EXISTS approach is typically the best solution. It directly checks that no order exists linking a salesperson to the company 'RED'. This keeps the query concise and avoids unnecessary aggregation while running in roughly O(n) time depending on indexing.
Is Sales Person asked at Google/Amazon/Meta?
Problems involving SQL joins and filtering relationships appear frequently in data engineering and backend interviews at companies like Amazon, Meta, and Google. Variants often test understanding of joins, anti-joins, and filtering rows that lack a relationship.
What data structure is used in Sales Person?
The problem relies on relational database tables and join operations rather than traditional data structures. SQL joins, subqueries, and anti-join patterns such as NOT EXISTS or LEFT JOIN with NULL filtering are the main techniques.
What is the time complexity of Sales Person?
Most SQL solutions run in O(n) time relative to the number of rows in the joined tables. Database indexes on sales_id or com_id can significantly improve join and lookup performance. Space complexity is effectively O(1) because the database engine streams results rather than storing large intermediate structures.