Won Revenue per Customer

Intermediate

Account 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

NameStageNameAmountCloseDateAccount
Acme oneClosed Won50,000today + 30 daysAcme Cloud
Acme twoClosed Won25,000today + 30 daysAcme Cloud
Acme threeProspecting90,000today + 30 daysAcme Cloud
Bluefin oneClosed Won40,000today + 30 daysBluefin Bank
Corex oneClosed Lost10,000today + 30 daysCorex Labs

Expected result on the sample

Nametotal
Acme Cloud75,000
Bluefin Bank40,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, then GROUP 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.

SOQL

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.