Problem

Table: Salesperson

1
2
3
4
5
6
7
8

+----------------+---------+
| 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

1
2
3
4
5
6
7
8
9

+----------------+------+
| 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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

+-------------+------+
| 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.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

    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.

Solution

Method 1 – LEFT JOIN and GROUP BY for Influence Calculation

Intuition

We need to sum the prices of all sales made by customers of each salesperson. If a salesperson has no customers or sales, their total should be 0. We use LEFT JOINs to ensure all salespeople are included, even if they have no customers or sales.

Approach

  1. LEFT JOIN Salesperson to Customer on salesperson_id.
  2. LEFT JOIN the result to Sales on customer_id.
  3. GROUP BY salesperson_id and name.
  4. Use COALESCE(SUM(price), 0) to handle salespeople with no sales.
  5. Return salesperson_id, name, and total_sales, ordered by salesperson_id ascending.

Code

1
2
3
4
5
6

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

Complexity

  • ⏰ Time complexity: O(n) — n is the number of salespeople, customers, and sales (single scan and join).
  • 🧺 Space complexity: O(1) — Only accumulators are used.