LeetCode 607 – Sales Person (SQL & Python) Solutions
This article is going to provide the solution to LeetCode 607 Sales Person. It’s an easy difficulty question.
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.
If you want to skip the article, check out the full YouTube video where I code the answer live.
LeetCode 607 SQL Solution
with all_sales as (   SELECT     SP.name as sales_name,     C.name as company_name   From SalesPerson SP   left join Orders O on O.sales_id = SP.sales_id   left join Company C on C.com_id = O.com_id ) SELECT distinct sales_name as name from all_sales where sales_name NOT IN (SELECT sales_name from all_sales where company_name = 'RED') The Common Table Expression (salary_ranks) first selects the following columns: SP.name (sales_name), C.name (company name). These columns are attained by joining 3 tables. SalesPerson, Orders and Company.
It joins the SalesPerson table (SP) with the Orders table (O) on the sales_id column, and then joins the result with the Company table (C) on the com_id column
The final query selects distinct sales_name from the all_sales CTE. It filters out salespeople whose sales_name appears in the result set where company_name is ‘RED’ in the all_sales CTE.
LeetCode 607 Python Solution
import pandas as pd def sales_person(sales_person: pd.DataFrame, company: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame: Â Â df = pd.merge(sales_person, orders, on='sales_id', how='left', suffixes=('_orders', '_sales')) Â Â all_sales = pd.merge(df, company, on='com_id', how='left', suffixes=('_orders', '_company')) Â Â all_sales_filtered = all_sales[~all_sales['sales_id'].isin(all_sales[all_sales['name_company'] == 'RED']['sales_id'].unique())] Â Â all_sales_filtered = all_sales_filtered.rename(columns={'name_orders':'name'}) Â Â all_sales_filtered2 = all_sales_filtered.drop_duplicates(subset=['name']) Â Â return all_sales_filtered2[['name']] The sales_person function first merges the sales_person DataFrame with the orders DataFrame based on the ‘sales_id’ column, using a left join. It uses the pd.merge() function for this purpose.Â
Suffixes ‘_orders’ and ‘_sales’ are added to distinguish overlapping columns. The result of this merge is stored in the DataFrame df.
Next, the function merges df with the company DataFrame based on the ‘com_id’ column, again using a left join. It uses the pd.merge() function for this purpose. Suffixes ‘_orders’ and ‘_company’ are added to distinguish overlapping columns. The result of this merge is stored in the DataFrame all_sales.
The function then filters out rows where the company name is ‘RED’. It does so by identifying the sales IDs associated with companies named ‘RED’, and then removing all rows with those sales IDs from the all_sales DataFrame. The filtered DataFrame is stored in all_sales_filtered
The function renames the ‘name_orders’ column to ‘name’ in the all_sales_filtered DataFrame.
The function drops duplicate rows based on the ‘name’ column in the all_sales_filtered DataFrame. This ensures that each salesperson appears only once. The result is stored in the DataFrame all_sales_filtered2.
Finally, the function returns a DataFrame containing only the ‘name’ column, which represents the names of salespeople who have not dealt with any companies named ‘RED’.
