Calculate the Influence of Each Salesperson - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min read

Problem Statement

Table: Salesperson

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| salesperson_id | int     |
| name           | varchar |
+----------------+---------+
salesperson_id contains unique values.
Each row in this table shows the ID of a salesperson.

Table: Customer

+----------------+------+
| Column Name    | Type |
+----------------+------+
| customer_id    | int  |
| salesperson_id | int  |
+----------------+------+
customer_id contains unique values.
salesperson_id is a foreign key (reference column) from the Salesperson table.
Each row in this table shows the ID of a customer and the ID of the salesperson.

Table: Sales

+-------------+------+
| Column Name | Type |
+-------------+------+
| sale_id     | int  |
| customer_id | int  |
| price       | int  |
+-------------+------+
sale_id contains unique values.
customer_id is a foreign key (reference column) from the Customer table.
Each row in this table shows ID of a customer and the price they paid for the sale with sale_id.

Write a solution to report the sum of prices paid by the customers of each salesperson. If a salesperson does not have any customers, the total value should be 0.

Return the result table in any order.

The result format is shown in the following example.

Example 1:

Input: 
Salesperson table:
+----------------+-------+
| salesperson_id | name  |
+----------------+-------+
| 1              | Alice |
| 2              | Bob   |
| 3              | Jerry |
+----------------+-------+
Customer table:
+-------------+----------------+
| customer_id | salesperson_id |
+-------------+----------------+
| 1           | 1              |
| 2           | 1              |
| 3           | 2              |
+-------------+----------------+
Sales table:
+---------+-------------+-------+
| sale_id | customer_id | price |
+---------+-------------+-------+
| 1       | 2           | 892   |
| 2       | 1           | 354   |
| 3       | 3           | 988   |
| 4       | 3           | 856   |
+---------+-------------+-------+
Output: 
+----------------+-------+-------+
| salesperson_id | name  | total |
+----------------+-------+-------+
| 1              | Alice | 1246  |
| 2              | Bob   | 1844  |
| 3              | Jerry | 0     |
+----------------+-------+-------+
Explanation: 
Alice is the salesperson for customers 1 and 2.
  - Customer 1 made one purchase with 354.
  - Customer 2 made one purchase with 892.
The total for Alice is 354 + 892 = 1246.

Bob is the salesperson for customers 3.
  - Customer 1 made one purchase with 988 and 856.
The total for Bob is 988 + 856 = 1844.

Jerry is not the salesperson of any customer.
The total for Jerry is 0.

Approach Overview

Problem Overview: Each salesperson is assigned a set of customers. Orders are placed by those customers. The task is to compute the influence of every salesperson, defined as the total value generated from orders placed by their customers. The result should list every salesperson along with their calculated influence, including those whose customers never placed an order.

Approach 1: Correlated Subquery Aggregation (O(n) time, O(1) extra space)

This approach calculates influence per salesperson using a correlated subquery. For each row in the Salesperson table, a nested query scans the Customer table to find customers linked to that salesperson, then aggregates order values from the Orders table. The influence becomes SUM(price) across those orders. While simple to read, correlated subqueries may execute repeatedly for each salesperson row, which can degrade performance on large datasets. It works well when the number of salespeople is small and indexing exists on foreign keys.

Approach 2: JOIN + GROUP BY Aggregation (O(n) time, O(n) space)

The efficient approach joins the three tables and performs aggregation once. Start with Salesperson, then LEFT JOIN the Customer table on salesperson_id, followed by another LEFT JOIN with Orders on customer_id. This creates a row for each order associated with a salesperson’s customers. Using SUM(o.price) with GROUP BY salesperson_id calculates the influence. The LEFT JOIN ensures salespeople without orders still appear with an influence of 0 (often handled with COALESCE). This pattern is standard SQL aggregation and scales well because the tables are scanned once.

The key insight is recognizing that influence depends on a relationship chain: Salesperson → Customer → Orders. Once those relationships are joined, computing influence becomes a straightforward aggregate operation.

This problem mainly tests your comfort with SQL joins and database relationships. It also reinforces aggregation patterns such as GROUP BY and SUM, which appear frequently in SQL interview questions.

Recommended for interviews: The JOIN + GROUP BY approach. Interviewers expect you to model the relationship across tables and compute the aggregate in a single query. The correlated subquery version demonstrates understanding but is typically less efficient and less common in production SQL.

Solution

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

Approach	Time	Space	When to Use
Correlated Subquery Aggregation	O(n)	O(1)	When writing quick queries or when the number of parent rows (salespeople) is small
JOIN + GROUP BY Aggregation	O(n)	O(n)	Best general solution; scales well for large datasets and commonly expected in SQL interviews

Video Solution

2372. Calculate the Influence of Each Salesperson (Leetcode Medium) • Programming Live with Larry • 105 views views

Frequently Asked Questions

Is Calculate the Influence of Each Salesperson easy or hard?

The problem is rated Medium but becomes straightforward if you are comfortable with SQL joins and aggregation. The main challenge is identifying the relationship chain between Salesperson, Customer, and Orders, then applying SUM with GROUP BY correctly.

Calculate the Influence of Each Salesperson Python/Java solution

This problem is designed for SQL, typically solved using MySQL queries. Python or Java would only be used if you were manually processing rows from the database, but in interviews and practice platforms the expected solution is a single SQL query using JOIN and GROUP BY.

How to solve Calculate the Influence of Each Salesperson in O(n)?

Use LEFT JOIN to connect Salesperson → Customer → Orders and compute SUM(o.price). Group the result by salesperson_id so all orders belonging to customers of the same salesperson are aggregated together. Using LEFT JOIN ensures salespeople with no orders still appear in the output with influence 0.

What is the best approach for Calculate the Influence of Each Salesperson?

The most efficient solution uses SQL JOINs combined with GROUP BY aggregation. Join the Salesperson table with Customer using salesperson_id, then join Orders using customer_id. Aggregate order values with SUM(price) and group by salesperson_id. This approach scans the tables once and runs in O(n) time.

Is Calculate the Influence of Each Salesperson asked at Google/Amazon/Meta?

SQL aggregation and join problems like this frequently appear in database interview rounds at companies such as Amazon, Meta, and Google. The exact problem may vary, but the required skill—joining relational tables and computing aggregates—is a common interview pattern.

What data structure is used in Calculate the Influence of Each Salesperson?

The problem relies on relational database tables and SQL operations rather than traditional in-memory data structures. Conceptually, the solution uses table joins and aggregation (SUM with GROUP BY) to compute totals across related rows.

What is the time complexity of Calculate the Influence of Each Salesperson?

The optimal JOIN + GROUP BY solution runs in O(n) time where n is the total number of rows processed across Salesperson, Customer, and Orders. The database performs joins and aggregation in a single pass with indexing support. Space complexity is O(n) for intermediate join results.

Ready to solve this problem?

Practice Calculate the Influence of Each Salesperson with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyMedium

Acceptance83.2%

Approaches1

Reading time4 min

Practice this problem

Open in Editor