Average sales cycle
mediumAverage sales cycle
Salesforce SQL Interview Question
Salesforce's revenue team wants to know how long each sales rep takes to win a deal, measured from the day an opportunity is created to the day it closes.
For each sales_rep, find the average number of days it took to close the deals they won, as avg_days_to_close, rounded to 1 decimal place. Sort the rows by avg_days_to_close, fastest first.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
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 | avg_days_to_close |
|---|---|
| Dana Brooks | 40 |
| Priya Nair | 42 |
| Marcus Hill | 46 |
Explanation
Marcus Hill won two deals in the example. Initech took 21 days, from January 20 to February 10, and Umbrella Health took 71 days, so the average is 46.0. The lost Vandelay Imports deal is not counted. Dana Brooks won one deal in 40 days and is listed first.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Salesforce
Difficulty
medium
Topic
dates
Your query
Starting the SQL engine...