{x}
blog image

Sales Person

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.

Solution Explanation and Code

This problem requires finding salespersons who haven't made any sales to the company named "RED". The optimal approach leverages SQL's JOIN and GROUP BY capabilities for efficient data manipulation.

Approach:

  1. JOIN: We perform a series of LEFT JOIN operations to combine data from the SalesPerson, Orders, and Company tables. A LEFT JOIN ensures that all rows from the left table (in this case, SalesPerson) are included in the result, even if there's no matching row in the right table(s).

  2. GROUP BY: We group the results by sales_id (each salesperson). This allows us to aggregate data for each salesperson.

  3. Conditional Aggregation: The core logic resides in the HAVING clause. We use SUM(c.name = 'RED') to count the number of orders where the company name is "RED" for each salesperson. The IFNULL function handles cases where a salesperson has no orders; it replaces NULL with 0.

  4. Filtering: The HAVING clause filters the results, keeping only those salespersons where the count of "RED" company orders is 0.

Time Complexity Analysis:

The time complexity is dominated by the JOIN operations. In the worst case, the complexity of a JOIN is O(n*m), where 'n' and 'm' are the number of rows in the tables being joined. However, database systems employ optimizations (like indexes) to significantly improve performance in practice. The GROUP BY and HAVING operations generally have a complexity related to the size of the intermediate result set after the JOIN. Therefore, the overall complexity is heavily dependent on the database system's optimization strategies and the size of the input tables. It's not straightforward to give a precise Big O notation without specific database implementation details.

Space Complexity Analysis:

The space complexity depends on the size of the intermediate result sets produced during the JOIN, GROUP BY, and HAVING operations. In the worst case, the space could grow proportionally to the size of the input tables, but this is also subject to database optimizations (like temporary tables and efficient storage).

MySQL Code:

SELECT s.name
FROM
    SalesPerson AS s
    LEFT JOIN Orders USING (sales_id)
    LEFT JOIN Company AS c USING (com_id)
GROUP BY sales_id
HAVING IFNULL(SUM(c.name = 'RED'), 0) = 0;

This query efficiently identifies the salespersons who have not made any sales to the "RED" company. The use of LEFT JOIN and conditional aggregation within the HAVING clause provides a concise and performant solution. Remember that actual performance can vary depending on database indexing and optimization strategies.