Sales Pipeline Value
ASKED IN INTERVIEWSalesforce
Problem Statement
Calculate the financial performance of sales reps by totaling their Closed Won and In Progress deals.
Rules:
- Output columns: sales_rep_id, closed_won_value, in_progress_value.
- Value = quantity * unit_price.
- Statuses to track: 'Closed Won' and 'In Progress'.
- Use 0 if no deals exist for a specific status.
- Results must be sorted by closed_won_value in descending order.
Tests your understanding of
Conditional Aggregation, Joins and Arithmetic Operations
Input Tables
sales_reps
| sales_rep_id(INTEGER) | rep_name(VARCHAR) |
|---|---|
| 1 | Marc Benioff |
| 2 | Amy Weaver |
| 3 | Parker Harris |
opportunities
| opp_id(INTEGER) | sales_rep_id(INTEGER) | product_id(INTEGER) | quantity(INTEGER) | status(VARCHAR) |
|---|---|---|---|---|
| 101 | 1 | 10 | 2 | Closed Won |
| 102 | 1 | 20 | 1 | In Progress |
| 103 | 2 | 10 | 5 | Closed Won |
| 104 | 3 | 10 | 3 | In Progress |
| 105 | 1 | 10 | 1 | Closed Won |
| 106 | 2 | 20 | 2 | In Progress |
products
| product_id(INTEGER) | unit_price(DECIMAL) |
|---|---|
| 10 | 1000 |
| 20 | 5000 |
Expected Output
| sales_rep_id(INTEGER) | closed_won_value(DECIMAL) | in_progress_value(DECIMAL) |
|---|---|---|
| 2 | 5000 | 10000 |
| 1 | 3000 | 5000 |
| 3 | 0 | 3000 |
Tags
MediumASKED IN INTERVIEWConditional AggregationJoinsArithmetic Operations
15-20 min
58%