Calculate the Influence of Each Salesperson - Problem

You are given three related tables: Salesperson, Customer, and Sales. Your task is to calculate the total sales influence of each salesperson by summing up all the sales made by their customers.

Write a SQL query to return the sum of prices paid by the customers of each salesperson. If a salesperson has no customers or their customers made no sales, the total should be 0.

Key Points:

  • Each salesperson can have multiple customers
  • Each customer can make multiple sales
  • Include all salespeople in the result, even those with no sales
  • Return results in any order

Table Schema

Salesperson
Column Name Type Description
salesperson_id PK int Unique identifier for each salesperson
name varchar Name of the salesperson
Primary Key: salesperson_id
Customer
Column Name Type Description
customer_id PK int Unique identifier for each customer
salesperson_id int Foreign key referencing Salesperson table
Primary Key: customer_id
Sales
Column Name Type Description
sale_id PK int Unique identifier for each sale
customer_id int Foreign key referencing Customer table
price int Price paid for the sale
Primary Key: sale_id

Input & Output

Example 1 — Multiple Sales per Customer
Input Tables:
Salesperson
salesperson_id name
1 Alice
2 Bob
3 Jerry
Customer
customer_id salesperson_id
1 1
2 1
3 2
Sales
sale_id customer_id price
1 1 500
2 2 1000
3 2 250
Output:
salesperson_id name total
1 Alice 1750
2 Bob 0
3 Jerry 0
💡 Note:

Alice has customers 1 and 2. Customer 1 made 1 sale ($500), customer 2 made 2 sales ($1000 + $250). Alice's total: $1750. Bob has customer 3 but no sales recorded, so total is $0. Jerry has no customers, so total is $0.
Example 2 — Salesperson with No Customers
Input Tables:
Salesperson
salesperson_id name
1 John
2 Amy
Customer
customer_id salesperson_id
1 1
Sales
sale_id customer_id price
1 1 2500
Output:
salesperson_id name total
1 John 2500
2 Amy 0
💡 Note:

John has customer 1 who made a sale of $2500. Amy has no customers, so her total is $0. The LEFT JOIN ensures Amy still appears in the result.
Example 3 — All Salespeople with No Sales
Input Tables:
Salesperson
salesperson_id name
1 Mike
2 Sarah
Customer
customer_id salesperson_id
Sales
sale_id customer_id price
Output:
salesperson_id name total
1 Mike 0
2 Sarah 0
💡 Note:

No customers or sales exist in the database, but all salespeople must still appear in the result with total = 0, demonstrating the importance of LEFT JOIN.

Constraints

  • 1 ≤ salesperson_id ≤ 10^5
  • 1 ≤ customer_id ≤ 10^5
  • 1 ≤ sale_id ≤ 10^5
  • 1 ≤ price ≤ 10^4
  • All IDs are unique within their respective tables

Visualization

Tap to expand
Calculate Salesperson Influence INPUT DATA Salespeople id name 1 Alice 2 Bob 3 Carol Customers cust_id sales_id amount 101 1 500 102 1 300 103 2 400 104 2 200 Relationship Sales Cust 1:N ALGORITHM STEPS 1 Initialize Map Create HashMap for each salesperson with total = 0 {Alice:0, Bob:0, Carol:0} 2 LEFT JOIN Tables Join salespeople with customers on sales_id 3 GROUP BY Salesperson Group joined results by salesperson id/name 4 SUM(amount) Calculate total influence using COALESCE for NULLs Alice: 500 + 300 = 800 Bob: 400 + 200 = 600 Carol: (no customers) = 0 FINAL RESULT Salesperson Influence name total_sales Alice 800 Bob 600 Carol 0 OK - All Salespeople Included (even with 0) Influence Distribution Alice 800 Bob 600 Carol 0 Key Insight: Use LEFT JOIN to ensure all salespeople appear in results, even those without customers. COALESCE(SUM(amount), 0) handles NULL values for salespeople with no sales, returning 0 instead. Time Complexity: O(n) where n = number of customer records. Space: O(m) for m salespeople. TutorialsPoint - Calculate the Influence of Each Salesperson | Optimal Solution

Related Problems

Asked in
Amazon 23 Microsoft 18 Google 15 Meta 12
28.5K Views
High Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen