00:00:00

Description

Editorial

Account Manager Revenue Influence Score

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Sales =

[{"sale_id":1001,"customer_id":11,"price":300},{"sale_id":1002,"customer_id":12,"price":450},{"sale_id":1003,"customer_id":20,"price":200},{"sale_id":1004,"customer_id":20,"price":700}]

Customer =

[{"customer_id":11,"salesperson_id":1},{"customer_id":12,"salesperson_id":1},{"customer_id":20,"salesperson_id":2}]

Salesperson =

[{"salesperson_id":1,"name":"Alice"},{"salesperson_id":2,"name":"Bob"},{"salesperson_id":3,"name":"Kim"}]

Loading problem...

101

00:00:00

Description

Editorial

Account Manager Revenue Influence Score

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Sales =

[{"sale_id":1001,"customer_id":11,"price":300},{"sale_id":1002,"customer_id":12,"price":450},{"sale_id":1003,"customer_id":20,"price":200},{"sale_id":1004,"customer_id":20,"price":700}]

Customer =

[{"customer_id":11,"salesperson_id":1},{"customer_id":12,"salesperson_id":1},{"customer_id":20,"salesperson_id":2}]

Salesperson =

[{"salesperson_id":1,"name":"Alice"},{"salesperson_id":2,"name":"Bob"},{"salesperson_id":3,"name":"Kim"}]

Account Manager Revenue Influence Score

Hints

Account Manager Revenue Influence Score

Hints