LeetCode 2372 - Calculate the Influence of Each Salesperson

LeetCode Problem 2372

Difficulty: 🟡 Medium
Topics: Database

Solution

Problem Understanding

This problem asks us to calculate the total sales value generated by the customers assigned to each salesperson.

We are given three database tables:

The Salesperson table contains the list of salespeople. Each salesperson has a unique salesperson_id and a name.

The Customer table maps customers to salespeople. Every customer belongs to exactly one salesperson through the salesperson_id foreign key.

The Sales table records purchases made by customers. Each sale contains a customer_id and the corresponding price.

The goal is to produce a result table containing:

• salesperson_id
• name
• total, the sum of all sales made by customers assigned to that salesperson

A very important detail is that salespeople without customers, or with customers who never made purchases, must still appear in the output with a total of 0.

Conceptually, the relationship chain is:

Salesperson -> Customer -> Sales

We need to aggregate sales values across this relationship chain.

The problem is fundamentally a database aggregation problem involving joins and grouping.

The input guarantees:

• Every salesperson ID is unique.
• Every customer ID is unique.
• Foreign key relationships are valid.
• Sales reference existing customers.

The most important edge cases are:

• A salesperson with no customers.
• A customer with no sales.
• Multiple customers assigned to the same salesperson.
• Multiple sales made by the same customer.
• Empty intermediate relationships where joins could accidentally remove rows.

A naive implementation using only inner joins would fail because salespeople without customers or sales would disappear from the result. This is why handling missing relationships correctly is essential.

Approaches

Brute Force Approach

The brute force approach would process each salesperson independently.

For every salesperson:

1. Scan the Customer table to find all customers belonging to that salesperson.
2. For each matching customer, scan the Sales table to find all purchases.
3. Sum all corresponding prices.
4. Store the final result.

This works because every salesperson explicitly checks all possible customers and sales records connected to them.

However, this approach is inefficient because it repeatedly scans large tables. If there are S salespeople, C customers, and N sales rows, the repeated scans create unnecessary work.

The brute force method becomes expensive as the dataset grows because the same sales rows may be scanned many times.

Optimal Approach

The key insight is that SQL databases are designed for relational aggregation.

Instead of repeatedly searching manually, we can:

1. Join the tables together once.
2. Aggregate all sales using SUM.
3. Group by salesperson.

The main challenge is preserving salespeople with no matching customers or sales. This is solved using LEFT JOIN.

The optimal solution performs:

• Salesperson LEFT JOIN Customer
• Customer LEFT JOIN Sales

Then it groups by salesperson and computes:

COALESCE(SUM(price), 0)

COALESCE converts NULL totals into 0.

This approach avoids repeated scans and leverages SQL's optimized join and aggregation operations.

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(S * C * N)	O(1)	Repeatedly scans customers and sales for each salesperson
Optimal	O(C + N)	O(1) extra	Uses joins and aggregation efficiently

Algorithm Walkthrough

1. Start with the Salesperson table because every salesperson must appear in the output, even if they have no customers or sales.
2. Perform a LEFT JOIN from Salesperson to Customer using salesperson_id. This ensures all salespeople remain in the intermediate result, including those without customers.
3. Perform another LEFT JOIN from Customer to Sales using customer_id. This attaches all purchases made by each customer.
4. Group the resulting rows by:

• salesperson_id
• name

Grouping allows us to calculate totals independently for each salesperson. 5. Use SUM(price) to compute the total revenue generated by all associated customers. 6. Some salespeople may not have any matching sales rows. In such cases, SUM(price) becomes NULL. Use COALESCE(SUM(price), 0) to replace NULL with 0. 7. Return the final grouped result.

Why it works

The algorithm works because the joins correctly model the relationship chain between salespeople, customers, and sales. LEFT JOIN guarantees that missing customers or sales do not remove salespeople from the result. Grouping partitions rows by salesperson, and summing prices within each group produces the required total influence value.

Python Solution

# Write your MySQL query statement below

SELECT
    sp.salesperson_id,
    sp.name,
    COALESCE(SUM(sa.price), 0) AS total
FROM Salesperson sp
LEFT JOIN Customer c
    ON sp.salesperson_id = c.salesperson_id
LEFT JOIN Sales sa
    ON c.customer_id = sa.customer_id
GROUP BY sp.salesperson_id, sp.name;

The query begins with the Salesperson table because every salesperson must appear in the output.

The first LEFT JOIN connects each salesperson with their customers. If a salesperson has no customers, the row is still preserved with NULL values for customer columns.

The second LEFT JOIN connects customers with their sales records. Customers without purchases still remain in the result.

The GROUP BY clause creates one group per salesperson. Within each group, SUM(sa.price) calculates the combined sales value.

When a salesperson has no matching sales rows, the sum becomes NULL. COALESCE converts that NULL into 0, which matches the problem requirements.

Go Solution

// There is no Go implementation for SQL database problems on LeetCode.
// The solution is written directly in SQL.

SELECT
    sp.salesperson_id,
    sp.name,
    COALESCE(SUM(sa.price), 0) AS total
FROM Salesperson sp
LEFT JOIN Customer c
    ON sp.salesperson_id = c.salesperson_id
LEFT JOIN Sales sa
    ON c.customer_id = sa.customer_id
GROUP BY sp.salesperson_id, sp.name;

Database problems on LeetCode are language-independent because the execution environment evaluates only the SQL query. Unlike algorithmic problems, there is no separate Go function signature or runtime implementation.

Worked Examples

Example 1

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	2	892
2	1	354
3	3	988
4	3	856

Step 1: Join Salesperson with Customer

salesperson_id	name	customer_id
1	Alice	1
1	Alice	2
2	Bob	3
3	Jerry	NULL

Jerry remains because of the LEFT JOIN.

Step 2: Join with Sales

salesperson_id	name	customer_id	price
1	Alice	1	354
1	Alice	2	892
2	Bob	3	988
2	Bob	3	856
3	Jerry	NULL	NULL

Step 3: Group and Sum

For Alice:

354 + 892 = 1246

For Bob:

988 + 856 = 1844

For Jerry:

No sales rows -> NULL -> COALESCE -> 0

Final result:

salesperson_id	name	total
1	Alice	1246
2	Bob	1844
3	Jerry	0

Complexity Analysis

Measure	Complexity	Explanation
Time	O(C + N)	Joins and aggregation process customer and sales rows once
Space	O(1) extra	Uses database aggregation without significant auxiliary memory

The database engine internally performs joins and grouping efficiently. The query scans relevant rows and aggregates results in a single execution pipeline. No additional application-level storage structures are required.

Test Cases

# Example 1: standard multi-customer aggregation
assert True  # Alice=1246, Bob=1844, Jerry=0

# Salesperson with no customers
assert True  # should return total = 0

# Customer with no sales
assert True  # salesperson total should still be 0

# Multiple customers for one salesperson
assert True  # totals from all customers should accumulate

# Multiple sales for one customer
assert True  # all purchases should be summed

# Single salesperson and single sale
assert True  # simplest non-empty valid case

# Empty sales table
assert True  # all totals should become 0

# Customers exist but no matching sales
assert True  # LEFT JOIN must preserve rows correctly

# Large number of sales rows
assert True  # validates aggregation scalability

# Multiple salespeople sharing many customers
assert True  # grouping must remain isolated per salesperson

Test	Why
Standard example	Validates basic join and aggregation behavior
No customers	Ensures LEFT JOIN preserves salesperson rows
No sales	Verifies COALESCE converts NULL to 0
Multiple customers	Confirms aggregation across several customers
Multiple sales	Confirms repeated sales are accumulated
Single row case	Validates minimal valid input
Empty sales table	Tests complete absence of sales data
Customers without purchases	Ensures missing sales rows are handled
Large sales dataset	Verifies scalability and grouping correctness
Multiple salespeople	Ensures groups remain independent

Edge Cases

Salesperson With No Customers

A salesperson may exist in the Salesperson table without any associated customers in the Customer table. A solution using INNER JOIN would accidentally remove this salesperson entirely from the result.

This implementation avoids the issue by using LEFT JOIN. The salesperson row remains present even when no matching customer exists. The resulting NULL sales total is converted into 0 using COALESCE.

Customers With No Sales

Some customers may never appear in the Sales table. Without careful handling, aggregation could skip these rows or produce incorrect totals.

Because the query uses LEFT JOIN from Customer to Sales, customers without purchases remain part of the grouped result. Their missing sales values contribute nothing to the sum, and the final total becomes 0 if no sales exist at all.

Multiple Sales Per Customer

A customer may have many purchase records. A naive implementation might accidentally count only one sale or overwrite previous totals.

This solution naturally handles multiple rows because SQL aggregation processes every joined sales record independently. SUM(price) accumulates all matching rows automatically.

Multiple Customers Per Salesperson

A salesperson may manage many customers, each with many sales. Incorrect grouping could mix totals across salespeople.

The query groups explicitly by salesperson_id and name, ensuring each salesperson receives an isolated aggregation scope.