Sales Person — LeetCode 607 Python Solution

The problem

Report the names of the salespeople who never had an order with the company named RED. The SalesPerson table holds sales_id (int, the primary key), name (varchar), salary (int), commission_rate (int) and hire_date (date). The Company table holds com_id (int, the primary key), name (varchar) and city (varchar). The Orders table holds order_id (int, the primary key), order_date (date), com_id (int), sales_id (int) and amount (int).

Example

SalesPerson table:

| sales_id | name  | salary | commission_rate | hire_date  |
| -------- | ----- | ------ | --------------- | ---------- |
| 1        | Amara | 90000  | 5               | 2019-03-01 |
| 2        | Ben   | 72000  | 8               | 2020-07-15 |
| 3        | Chen  | 85000  | 6               | 2021-01-09 |

Company table:

| com_id | name   | city   |
| ------ | ------ | ------ |
| 1      | RED    | Boston |
| 2      | ORANGE | Newark |

Orders table:

| order_id | order_date | com_id | sales_id | amount |
| -------- | ---------- | ------ | -------- | ------ |
| 1        | 2024-01-10 | 1      | 2        | 15000  |
| 2        | 2024-02-14 | 2      | 3        | 9000   |

Result:

| name  |
| ----- |
| Amara |
| Chen  |

Ben is excluded because his order went to company 1, which is RED; Chen only sold to ORANGE and Amara has no orders at all.

Python solution

import pandas as pd

def sales_person(salesperson: pd.DataFrame, company: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame:
    red_ids = company.loc[company['name'] == 'RED', 'com_id']
    red_sales = orders[orders['com_id'].isin(red_ids)]['sales_id'].unique()
    res = salesperson[~salesperson['sales_id'].isin(red_sales)]
    return res[['name']]

Complexity

Related problems