Domain 1EasyCORE

Sales Person

**Restated Problem:** We are given two tables in a database: 1. `SalesPerson` table with columns: `sales_id`, `name`. 2. `Orders` table with columns: `order_id`, `date`, `amount`, `commission_rate`, `sales_id`, `customer_id`.

Domain: Easy•Updated: 2026-08-31
#Algorithms#LeetCode#qwen2.5-coder:14b#**FAANG asked:** 0

607. Sales Person

Model: qwen2.5-coder:14b
Topics:
FAANG asked: 0
Acceptance rate: 65.4%


Problem Breakdown

Restated Problem: We are given two tables in a database:

  1. SalesPerson table with columns: sales_id, name.
  2. Orders table with columns: order_id, date, amount, commission_rate, sales_id, customer_id.

The task is to find the names of all salespersons who did not make any sales to a customer in the Red region.

Inputs:

  • SalesPerson: A list of salespersons with their IDs and names.
  • Orders: A list of orders with details including the salesperson ID, customer ID, and other order-related information.
  • Customers: A list of customers with their IDs, names, and regions.

Outputs:

  • A list of names of salespersons who did not make any sales to a customer in the Red region.

Implicit Constraints/Edge Cases:

  • The Orders table might be empty, meaning no sales have been made.
  • There could be duplicate entries in the Orders table for the same sale.
  • The Customers table might contain customers not present in any orders.
  • The SalesPerson table might contain salespersons with no associated orders.

Intuition

Brute Force Approach:

  1. Iterate through each salesperson.
  2. For each salesperson, check all their orders.
  3. For each order, find the corresponding customer and check if they are in the Red region.
  4. If a salesperson has made any sale to a Red region customer, exclude them from the result.

This approach is inefficient because it involves multiple nested loops and database joins, leading to high time complexity (O(n^3) in the worst case).

Key Insight: Instead of checking each order for each salesperson, we can first identify all customers in the Red region. Then, find all orders made to these customers. Finally, exclude any salespersons who are associated with these orders.

This reduces the problem to a series of efficient set operations, significantly improving time complexity.

Approach

  1. Identify Red Region Customers:

    • Query the Customers table to get a list of customer IDs that belong to the Red region.
  2. Find Orders to Red Region Customers:

    • Use the list of red region customers to filter orders from the Orders table, resulting in a list of salesperson IDs who have made sales to these customers.
  3. Exclude Salespersons with Red Region Sales:

    • Query the SalesPerson table to get all salesperson names.
    • Exclude any salespersons whose IDs are present in the list obtained from step 2.

Data Structures Chosen:

  • Use sets for efficient membership testing and exclusion operations.

Complexity Analysis

Time Complexity:

  • O(n) for querying the Customers table to find red region customers.
  • O(m) for filtering orders related to these customers, where m is the number of orders.
  • O(k) for querying all salespersons, where k is the number of salespersons.

Overall time complexity is O(n + m + k), which is efficient given that it avoids nested loops.

Space Complexity:

  • O(r) for storing red region customer IDs.
  • O(s) for storing salesperson IDs associated with red region orders.

Overall space complexity is O(r + s).

Solution

def sales_person_not_in_red_region(sales_person, orders, customers):
    # Step 1: Identify Red Region Customers
    red_customers = {customer_id for customer_id, name, region in customers if region == 'Red'}
    
    # Step 2: Find Orders to Red Region Customers
    red_salesperson_ids = {order.sales_id for order in orders if order.customer_id in red_customers}
    
    # Step 3: Exclude Salespersons with Red Region Sales
    result = [sales_person.name for sales_person in sales_person if sales_person.sales_id not in red_salesperson_ids]
    
    return result

# Example usage:
# Assuming sales_person, orders, and customers are lists of objects with the respective attributes.

Edge Cases Handled

  • Empty Orders Table: If there are no orders, red_salesperson_ids will be empty, and all salespersons will be included in the result.
  • No Red Region Customers: If no customers are in the Red region, red_customers will be empty, and all salespersons will be included in the result.
  • Salespersons with No Orders: These salespersons will naturally not be present in red_salesperson_ids, so they will be included in the result.