Sales Reps Without RED Orders

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders =

[{"order_id":1,"order_date":"2014-01-01","com_id":3,"sales_id":4,"amount":10000},{"order_id":2,"order_date":"2014-02-01","com_id":4,"sales_id":5,"amount":5000},{"order_id":3,"order_date":"2014-03-01","com_id":1,"sales_id":1,"amount":50000},{"order_id":4,"order_date":"2014-04-01","com_id":1,"sales_id":4,"amount":25000}]

company =

[{"com_id":1,"name":"RED","city":"Boston"},{"com_id":2,"name":"ORANGE","city":"New York"},{"com_id":3,"name":"YELLOW","city":"Boston"},{"com_id":4,"name":"GREEN","city":"Austin"}]

salesperson =

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

Sales Reps Without RED Orders

Hints

Sales Reps Without RED Orders

Hints