Account Manager Revenue Influence Score

Medium

A revenue operations team needs a weekly influence report for account managers. Each customer account is owned by exactly one salesperson, and every sale belongs to a customer account. Table: Salesperson salesperson_id: unique identifier of a salesperson name: salesperson name Table: Customer customer_id: unique identifier of a customer account salesperson_id: owner salesperson for that customer Table: Sales sale_id: unique sale event identifier customer_id: customer account for that sale event price: paid amount for the sale event Influence metric: A salesperson's total influence is the sum of Sales.price across all sale rows made by customers assigned to that salesperson. Important rules: Include every salesperson in the output. If a salesperson has no customers, total must be 0. If a salesperson has customers but none of them has sales rows, total must still be 0. Task: Return one row per salesperson with columns: salesperson_id name total Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Salesperson:
| salesperson_id | name  |
|----------------|-------|
| 1              | Alice |
| 2              | Bob   |
| 3              | Kim   |

Customer:
| customer_id | salesperson_id |
|-------------|----------------|
| 11          | 1              |
| 12          | 1              |
| 20          | 2              |

Sales:
| sale_id | customer_id | price |
|---------|-------------|-------|
| 1001    | 11          | 300   |
| 1002    | 12          | 450   |
| 1003    | 20          | 200   |
| 1004    | 20          | 700   |

Output

[
  {"salesperson_id":1,"name":"Alice","total":750},
  {"salesperson_id":2,"name":"Bob","total":900},
  {"salesperson_id":3,"name":"Kim","total":0}
]

Explanation

Alice owns customers 11 and 12, Bob owns customer 20, and Kim owns no customers, so Kim appears with total 0.

Example 2

Input

Salesperson:
| salesperson_id | name   |
|----------------|--------|
| 10             | Nora   |
| 20             | Priya  |

Customer:
| customer_id | salesperson_id |
|-------------|----------------|
| 100         | 10             |
| 101         | 10             |
| 200         | 20             |

Sales:
| sale_id | customer_id | price |
|---------|-------------|-------|
| 1       | 100         | 50    |
| 2       | 100         | 30    |

Output

[
  {"salesperson_id":10,"name":"Nora","total":80},
  {"salesperson_id":20,"name":"Priya","total":0}
]

Explanation

Priya has a customer assignment but no sales rows for that customer, so Priya still appears with total 0.

Example 3

Input

Salesperson:
| salesperson_id | name |
|----------------|------|
| 99             | Omar |

Customer:
| customer_id | salesperson_id |
|-------------|----------------|

Sales:
| sale_id | customer_id | price |
|---------|-------------|-------|

Output

[
  {"salesperson_id":99,"name":"Omar","total":0}
]

Explanation

With no customers and no sales, the salesperson must still be returned with zero total.

Constraints

0 <= number of rows in Salesperson <= 120
0 <= number of rows in Customer <= 400
0 <= number of rows in Sales <= 900
1 <= salesperson_id <= 1000000000
1 <= customer_id <= 1000000000
1 <= sale_id <= 1000000000
1 <= price <= 1000000
salesperson_id values are unique in Salesperson
customer_id values are unique in Customer
sale_id values are unique in Sales
Each customer row references an existing salesperson
Each sales row references an existing customer
Return exactly columns: salesperson_id, name, total
Include every salesperson exactly once
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: salesperson_id, name, total

Loading problem...

Preparing your editor. You can read the problem while it loads.

Account Manager Revenue Influence Score

Medium

A revenue operations team needs a weekly influence report for account managers. Each customer account is owned by exactly one salesperson, and every sale belongs to a customer account. Table: Salesperson salesperson_id: unique identifier of a salesperson name: salesperson name Table: Customer customer_id: unique identifier of a customer account salesperson_id: owner salesperson for that customer Table: Sales sale_id: unique sale event identifier customer_id: customer account for that sale event price: paid amount for the sale event Influence metric: A salesperson's total influence is the sum of Sales.price across all sale rows made by customers assigned to that salesperson. Important rules: Include every salesperson in the output. If a salesperson has no customers, total must be 0. If a salesperson has customers but none of them has sales rows, total must still be 0. Task: Return one row per salesperson with columns: salesperson_id name total Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Salesperson:
| salesperson_id | name  |
|----------------|-------|
| 1              | Alice |
| 2              | Bob   |
| 3              | Kim   |

Customer:
| customer_id | salesperson_id |
|-------------|----------------|
| 11          | 1              |
| 12          | 1              |
| 20          | 2              |

Sales:
| sale_id | customer_id | price |
|---------|-------------|-------|
| 1001    | 11          | 300   |
| 1002    | 12          | 450   |
| 1003    | 20          | 200   |
| 1004    | 20          | 700   |

Output

[
  {"salesperson_id":1,"name":"Alice","total":750},
  {"salesperson_id":2,"name":"Bob","total":900},
  {"salesperson_id":3,"name":"Kim","total":0}
]

Explanation

Alice owns customers 11 and 12, Bob owns customer 20, and Kim owns no customers, so Kim appears with total 0.

Example 2

Input

Salesperson:
| salesperson_id | name   |
|----------------|--------|
| 10             | Nora   |
| 20             | Priya  |

Customer:
| customer_id | salesperson_id |
|-------------|----------------|
| 100         | 10             |
| 101         | 10             |
| 200         | 20             |

Sales:
| sale_id | customer_id | price |
|---------|-------------|-------|
| 1       | 100         | 50    |
| 2       | 100         | 30    |

Output

[
  {"salesperson_id":10,"name":"Nora","total":80},
  {"salesperson_id":20,"name":"Priya","total":0}
]

Explanation

Priya has a customer assignment but no sales rows for that customer, so Priya still appears with total 0.

Example 3

Input

Salesperson:
| salesperson_id | name |
|----------------|------|
| 99             | Omar |

Customer:
| customer_id | salesperson_id |
|-------------|----------------|

Sales:
| sale_id | customer_id | price |
|---------|-------------|-------|

Output

[
  {"salesperson_id":99,"name":"Omar","total":0}
]

Explanation

With no customers and no sales, the salesperson must still be returned with zero total.

Constraints

0 <= number of rows in Salesperson <= 120
0 <= number of rows in Customer <= 400
0 <= number of rows in Sales <= 900
1 <= salesperson_id <= 1000000000
1 <= customer_id <= 1000000000
1 <= sale_id <= 1000000000
1 <= price <= 1000000
salesperson_id values are unique in Salesperson
customer_id values are unique in Customer
sale_id values are unique in Sales
Each customer row references an existing salesperson
Each sales row references an existing customer
Return exactly columns: salesperson_id, name, total
Include every salesperson exactly once
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: salesperson_id, name, total