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.
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'.
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.
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.
This 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.
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.
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).
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.
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).
| 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. |
How To Find Good Salespeople • Dan Lok • 51,077 views views
Watch 9 more video solutions →Practice Sales Person with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor