Calculate the Influence of Each Salesperson - Problem

Imagine you're analyzing a sales organization to determine the influence of each salesperson based on the total revenue generated by their customers.

You have three interconnected tables:

  • Salesperson: Contains salesperson information
  • Customer: Maps customers to their assigned salesperson
  • Sales: Records individual sale transactions with prices

Your task is to calculate the total sales influence of each salesperson by summing up all the sales made by their assigned customers. If a salesperson has no customers or their customers made no sales, their influence should be 0.

Think of it like calculating commission potential - each salesperson's value is determined by the total revenue their customer base generates!

Input & Output

example_1.sql — Basic Case
$ Input: Salesperson: [(1, 'Alice'), (2, 'Bob')] Customer: [(101, 1), (102, 1)] Sales: [(1, 101, 100), (2, 102, 200)]
› Output: [(1, 'Alice', 300), (2, 'Bob', 0)]
💡 Note: Alice has 2 customers (101, 102) with sales totaling $300. Bob has no customers, so his total is $0.
example_2.sql — Mixed Case
$ Input: Salesperson: [(1, 'John'), (2, 'Jane'), (3, 'Mike')] Customer: [(201, 1), (202, 2)] Sales: [(1, 201, 150), (2, 201, 250), (3, 202, 300)]
› Output: [(1, 'John', 400), (2, 'Jane', 300), (3, 'Mike', 0)]
💡 Note: John's customer 201 made 2 sales ($150 + $250 = $400). Jane's customer 202 made 1 sale ($300). Mike has no customers.
example_3.sql — All Zero Case
$ Input: Salesperson: [(1, 'Tom'), (2, 'Sue')] Customer: [] Sales: []
› Output: [(1, 'Tom', 0), (2, 'Sue', 0)]
💡 Note: No customers or sales exist, so all salespeople have $0 total. LEFT JOIN ensures all salespeople are included.

Visualization

Tap to expand
SalespeopleAlice, BobCharlieLEFTJOINCustomersC1→AliceC2→BobLEFTJOINSalesC1: $300C2: $200Final Influence ScoresAlice: $300 (has customers with sales)Bob: $200 (has customers with sales)Charlie: $0 (no customers, preserved by LEFT JOIN)
Understanding the Visualization
1
Start with Salespeople
Begin with the complete list of all salespeople
2
Add Customer Relationships
LEFT JOIN preserves salespeople even without customers
3
Include Sales Data
Another LEFT JOIN adds sales while keeping all previous rows
4
Calculate Totals
GROUP BY and SUM aggregate the final influence scores
Key Takeaway
🎯 Key Insight: LEFT JOIN ensures all salespeople are included in the result, even those with zero sales, making the query complete and accurate.

Time & Space Complexity

Time Complexity
⏱️
O(n + m + k)

Single pass through all tables where n=salespeople, m=customers, k=sales

n
2n
✓ Linear Growth
Space Complexity
O(1)

No additional space needed beyond result aggregation

n
2n
✓ Linear Space

Related Problems

Constraints

  • 1 ≤ salesperson_id ≤ 105
  • 1 ≤ customer_id ≤ 105
  • 1 ≤ sale_id ≤ 105
  • 1 ≤ price ≤ 104
  • Each salesperson_id, customer_id, and sale_id is unique
Asked in
Amazon 45 Microsoft 38 Google 32 Meta 28
24.3K Views
Medium Frequency
~15 min Avg. Time
856 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