Biggest Open Deal per Stage
Intermediate SolvedSales managers review the single largest deal still in play at each stage of the pipeline.
For Opportunities that are still open, return each StageName with the largest Amount, aliased biggest.
Sample data
Opportunity 4 records
| Name | StageName | Amount | CloseDate |
|---|---|---|---|
| A | Prospecting | 20,000 | today + 30 days |
| B | Prospecting | 45,000 | today + 30 days |
| C | Negotiation/Review | 80,000 | today + 30 days |
| D | Closed Won | 500,000 | today + 30 days |
Expected result on the sample
| StageName | biggest |
|---|---|
| Prospecting | 45,000 |
| Negotiation/Review | 80,000 |
Row order does not matter. Only the columns above are compared; extra columns in your SELECT are fine.
Hints
IsClosed = falsekeeps open deals.MAX(Amount) biggest, grouped by stage.
What this tests
IsClosed is set from the stage configuration, so filtering on it keeps working when an admin adds or renames closed stages, where a hard-coded list of stage names would not. MAX, MIN, SUM and AVG all ignore nulls, so a deal with no amount never wins MAX.
Datasets
Solved on this device only. Sign in with Google to keep your progress across devices.
Run on sample executes your query against the sample data shown on the left and displays the rows it returns. Submit checks it against the sample plus 2 hidden datasets, comparing your rows with the expected rows on each. Queries run in a real Salesforce org; the data is inserted for your run and rolled back afterwards.