Sales Person LeetCode Solution

Last updated on October 9th, 2024 at 10:15 pm

This Leetcode problem Sales Person LeetCode Solution is done in SQL.

List of all LeetCode Solution

Level of Question

Easy

Sales Person LeetCode Solution

Sales Person LeetCode Solution

Problem Statement

Column NameType
sales_idint
namevarchar
salaryint
commission_rateint
hire_datedate
Table: SalesPerson

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.

Column NameType
com_idint
namevarchar
cityvarchar
Table: Company

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.

Column NameType
order_idint
order_datedate
com_idint
sales_idint
amountint
Table: Orders

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:

sales_idname salary commission_rate hire_date
1John10000064/1/2006
2Amy1200055/1/2010
3Mark650001212/25/2008
4Pam25000251/1/2005
5Alex5000102/3/2007
SalesPerson table:
com_idnamecity
1REDBoston
2ORANGENew York
3YELLOWBoston
4GREENAustin
Company table:
order_id order_datecom_id sales_id amount
11/1/2014341000
22/1/2014455000
33/1/20141150000
44/1/20141425000
Orders table:

Output:

name
Amy
Mark
Alex

Explanation: According to orders 3 and 4 in the Orders table, it is easy to tell that only salespersons John and Pam have sales to company RED, so we report all the other names in the table salespersons.

1. Sales Person LeetCode Solution MySQL

select 
  s.name 
from 
  salesperson as s 
where 
  s.sales_id not in(
    select 
      sales_id 
    from 
      orders as o 
      left join company as c on o.com_id = c.com_id 
    where 
      c.name = 'RED'
  );
Scroll to Top