Sales Reps Without RED Orders

Easy

Your sales operations warehouse contains three tables: salesperson(sales_id, name, salary, commission_rate, hire_date) company(com_id, name, city) orders(order_id, order_date, com_id, sales_id, amount) Build a report of sales representatives who never processed an order for a company whose name is exactly "RED". Rules: A representative must be excluded if they have at least one matching order tied to any company row with name = "RED". If a representative only sold to non-RED companies, they should be included. If multiple companies are named "RED", all of them count as restricted. Data quality note: orders rows with null sales_id or null com_id do not create a valid salesperson-company relationship. Output requirements: Return exactly one column named: name. Output order is not important. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

salesperson:
| sales_id | name | salary | commission_rate | hire_date  |
|----------|------|--------|-----------------|------------|
| 1        | John | 100000 | 6               | 2006-04-01 |
| 2        | Amy  | 12000  | 5               | 2010-05-01 |
| 3        | Mark | 65000  | 12              | 2008-12-25 |
| 4        | Pam  | 25000  | 25              | 2005-01-01 |
| 5        | Alex | 5000   | 10              | 2007-02-03 |

company:
| com_id | name   | city     |
|--------|--------|----------|
| 1      | RED    | Boston   |
| 2      | ORANGE | New York |
| 3      | YELLOW | Boston   |
| 4      | GREEN  | Austin   |

orders:
| order_id | order_date  | com_id | sales_id | amount |
|----------|-------------|--------|----------|--------|
| 1        | 2014-01-01  | 3      | 4        | 10000  |
| 2        | 2014-02-01  | 4      | 5        | 5000   |
| 3        | 2014-03-01  | 1      | 1        | 50000  |
| 4        | 2014-04-01  | 1      | 4        | 25000  |

Output

[
  {"name":"Alex"},
  {"name":"Amy"},
  {"name":"Mark"}
]

Explanation

John and Pam both sold to RED, so they are excluded. The other three are included.

Example 2

Input

salesperson:
| sales_id | name  | salary | commission_rate | hire_date  |
|----------|-------|--------|-----------------|------------|
| 10       | Nia   | 45000  | 8               | 2012-06-10 |
| 11       | Omar  | 61000  | 11              | 2011-03-17 |

company:
| com_id | name   | city   |
|--------|--------|--------|
| 7      | BLUE   | Denver |
| 8      | SILVER | Miami  |

orders:
| order_id | order_date  | com_id | sales_id | amount |
|----------|-------------|--------|----------|--------|
| 101      | 2020-07-09  | 7      | 10       | 2500   |
| 102      | 2020-08-12  | 8      | 11       | 3000   |

Output

[
  {"name":"Nia"},
  {"name":"Omar"}
]

Explanation

No company is named RED, so everyone is included.

Example 3

Input

salesperson:
| sales_id | name | salary | commission_rate | hire_date  |
|----------|------|--------|-----------------|------------|
| 21       | Ivo  | 70000  | 9               | 2014-01-09 |
| 22       | Kea  | 72000  | 10              | 2015-11-20 |
| 23       | Lia  | 50000  | 7               | 2019-02-14 |

company:
| com_id | name | city      |
|--------|------|-----------|
| 31     | RED  | Seattle   |
| 32     | RED  | Portland  |
| 33     | Teal | San Diego |

orders:
| order_id | order_date  | com_id | sales_id | amount |
|----------|-------------|--------|----------|--------|
| 201      | 2021-01-01  | 32     | 21       | 9000   |
| 202      | 2021-01-11  | 33     | 21       | 1200   |
| 203      | 2021-01-13  | 33     | 22       | 1800   |

Output

[
  {"name":"Kea"},
  {"name":"Lia"}
]

Explanation

Ivo has a RED order via com_id=32 and is excluded. Kea and Lia have no RED orders.

Constraints

1 <= number of rows in salesperson <= 10,000
1 <= number of rows in company <= 2,000
0 <= number of rows in orders <= 200,000
salesperson.sales_id is unique
company.com_id is unique
orders.order_id is unique
company.name can repeat; more than one company may be named RED
In imperfect feeds, orders.sales_id or orders.com_id may be null and should not count as valid matches
Use SQLite 3.27.2 behavior for SQL submissions
For Pandas submissions, return a DataFrame with exactly one column: name

salesperson: | sales_id | name | salary | commission_rate | hire_date | |----------|------|--------|-----------------|------------| | 1 | John | 100000 | 6 | 2006-04-01 | | 2 | Amy | 12000 | 5 | 2010-05-01 | | 3 | Mark | 65000 | 12 | 2008-12-25 | | 4 | Pam | 25000 | 25 | 2005-01-01 | | 5 | Alex | 5000 | 10 | 2007-02-03 | company: | com_id | name | city | |--------|--------|----------| | 1 | RED | Boston | | 2 | ORANGE | New York | | 3 | YELLOW | Boston | | 4 | GREEN | Austin | orders: | order_id | order_date | com_id | sales_id | amount | |----------|-------------|--------|----------|--------| | 1 | 2014-01-01 | 3 | 4 | 10000 | | 2 | 2014-02-01 | 4 | 5 | 5000 | | 3 | 2014-03-01 | 1 | 1 | 50000 | | 4 | 2014-04-01 | 1 | 4 | 25000 |

salesperson: | sales_id | name | salary | commission_rate | hire_date | |----------|-------|--------|-----------------|------------| | 10 | Nia | 45000 | 8 | 2012-06-10 | | 11 | Omar | 61000 | 11 | 2011-03-17 | company: | com_id | name | city | |--------|--------|--------| | 7 | BLUE | Denver | | 8 | SILVER | Miami | orders: | order_id | order_date | com_id | sales_id | amount | |----------|-------------|--------|----------|--------| | 101 | 2020-07-09 | 7 | 10 | 2500 | | 102 | 2020-08-12 | 8 | 11 | 3000 |

salesperson: | sales_id | name | salary | commission_rate | hire_date | |----------|------|--------|-----------------|------------| | 21 | Ivo | 70000 | 9 | 2014-01-09 | | 22 | Kea | 72000 | 10 | 2015-11-20 | | 23 | Lia | 50000 | 7 | 2019-02-14 | company: | com_id | name | city | |--------|------|-----------| | 31 | RED | Seattle | | 32 | RED | Portland | | 33 | Teal | San Diego | orders: | order_id | order_date | com_id | sales_id | amount | |----------|-------------|--------|----------|--------| | 201 | 2021-01-01 | 32 | 21 | 9000 | | 202 | 2021-01-11 | 33 | 21 | 1200 | | 203 | 2021-01-13 | 33 | 22 | 1800 |