Accounts and Their Open Cases
Intermediate SolvedAn account health view lists every account with the cases that are still open against it, including accounts that have none.
Return every Account's Name with a subquery of its Cases whose Status is not Closed. Accounts without open cases must still appear. The grader counts the rows in your Cases subquery.
Sample data
Account 3 records
| Name |
|---|
| Acme Cloud |
| Bluefin Bank |
| Corex Labs |
Case 3 records
| Subject | Status | Priority | Account |
|---|---|---|---|
| Login fails | New | High | Acme Cloud |
| Old invoice | Closed | Low | Acme Cloud |
| API timeout | Working | Medium | Bluefin Bank |
Expected result on the sample
| Name | Cases (rows in subquery) |
|---|---|
| Acme Cloud | 1 |
| Bluefin Bank | 1 |
| Corex Labs | 0 |
Row order does not matter. Only the columns above are compared; extra columns in your SELECT are fine.
Hints
- The relationship name from Account to Case is
Cases. - Put the Status filter inside the subquery, not on the outer query.
What this tests
A parent-to-child subquery uses the plural relationship name (Cases, Contacts, Opportunities) and returns each parent once with its children nested. Unlike a semi-join, it keeps parents that have no matching children. In Apex you read the children with account.Cases, and a large child list should be iterated directly to avoid a QueryException.
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.