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.
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.
MySQL
| 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 |
LeetCode Medium 2372 “Influence of Each Salesperson" Interview SQL Question Explanation | EDS • Everyday Data Science • 1,058 views views
Watch 1 more video solutions →Practice Calculate the Influence of Each Salesperson with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor