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
Salesperson Influence CalculationInput TablesSalespersonAlice, Bob, JerryCustomerCust1→Alice, Cust2→AliceSales$500, $1000, $250LEFT JOIN+ GROUP BYSQL Process1. LEFT JOIN preservesall salespeople2. SUM() aggregatessales per personResultFinal OutputnametotalAlice$1750Bob$0Jerry$0
Understanding the Visualization
1
Input Tables
Three related tables: Salesperson, Customer, Sales
2
LEFT JOIN Chain
Preserve all salespeople while linking to their sales
3
Aggregate
GROUP BY and SUM to calculate totals per salesperson
Key Takeaway
🎯 Key Insight: LEFT JOIN ensures all salespeople appear in results, even with zero sales

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