Won Revenue per Customer
Intermediate SolvedAccount managers want the total value of the deals each customer has signed.
For Opportunities in stage Closed Won, return each account's name (Account.Name) and the total Amount, aliased total. Group by the account name.
Sample data
Account 3 records
| Name |
|---|
| Acme Cloud |
| Bluefin Bank |
| Corex Labs |
Opportunity 5 records
| Name | StageName | Amount | CloseDate | Account |
|---|---|---|---|---|
| Acme one | Closed Won | 50,000 | today + 30 days | Acme Cloud |
| Acme two | Closed Won | 25,000 | today + 30 days | Acme Cloud |
| Acme three | Prospecting | 90,000 | today + 30 days | Acme Cloud |
| Bluefin one | Closed Won | 40,000 | today + 30 days | Bluefin Bank |
| Corex one | Closed Lost | 10,000 | today + 30 days | Corex Labs |
Expected result on the sample
| Name | total |
|---|---|
| Acme Cloud | 75,000 |
| Bluefin Bank | 40,000 |
Row order does not matter. Only the columns above are compared; extra columns in your SELECT are fine.
Hints
- Filter the stage in WHERE.
SUM(Amount) total, thenGROUP BY Account.Name.
What this tests
You can group by a parent field such as Account.Name. In the AggregateResult the grouped field comes back under its own name, so you read it with ar.get('Name'). The WHERE clause runs before grouping, which is how only won deals reach the SUM; HAVING is for conditions on the totals themselves.
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.