Win rate by sales rep
mediumWin rate by sales rep
Salesforce SQL Interview Question
Salesforce's sales operations team measures each rep's win rate: of the deals that have finished, how many did the rep win?
Find each sales_rep and their win rate as win_rate_pct, rounded to 1 decimal place. The win rate is the percentage of a rep's closed deals that were won. Deals that are still open do not count.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Analytics Engineer
opportunitiesTable12 rows
| Column Name | Type |
|---|---|
| opportunity_id | BIGINT |
| account_name | VARCHAR |
| sales_rep | VARCHAR |
| stage | VARCHAR |
| amount | BIGINT |
| created_date | DATE |
| close_date | DATE |
opportunitiesExample Input
| opportunity_id | account_name | sales_rep | stage | amount | created_date | close_date |
|---|---|---|---|---|---|---|
| 1 | Acme Corp | Priya Nair | Closed Won | 48000 | 2024-01-08 | 2024-02-19 |
| 2 | Globex | Priya Nair | Closed Lost | 22000 | 2024-01-15 | 2024-03-01 |
| 3 | Initech | Marcus Hill | Closed Won | 15000 | 2024-01-20 | 2024-02-10 |
| 4 | Umbrella Health | Marcus Hill | Closed Won | 67000 | 2024-02-01 | 2024-04-12 |
| 5 | Stark Logistics | Dana Brooks | Closed Lost | 31000 | 2024-02-05 | 2024-03-18 |
| 6 | Wayne Retail | Dana Brooks | Closed Won | 54000 | 2024-02-11 | 2024-03-22 |
| 7 | Hooli | Priya Nair | Closed Lost | 39000 | 2024-02-20 | 2024-03-30 |
| 8 | Vandelay Imports | Marcus Hill | Closed Lost | 12000 | 2024-03-01 | 2024-03-25 |
Example Output
| sales_rep | win_rate_pct |
|---|---|
| Dana Brooks | 50 |
| Marcus Hill | 66.7 |
| Priya Nair | 33.3 |
Explanation
Marcus Hill won 2 of 3 closed deals, which is 66.7%, and Priya Nair won 1 of 3, which is 33.3%. Dana Brooks won 1 of 2, which is 50.0%. Deals that are still in progress are not counted as losses.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Salesforce
Difficulty
medium
Topic
conditional logic
Your query
Starting the SQL engine...