User Story
As a sales manager at ABC Company, I need to be able to quickly view information about our Accounts and their related Contacts, Opportunities, and Cases, so that I can make data-driven decisions about our sales and support strategies. Specifically, I want to be able to view the 50 most recent Accounts with BillingCountry set to 'United States' and BillingState set to either 'CA' or 'NY', along with their associated Contacts whose MailingPostalCode is '94105', Opportunities whose StageName is 'Closed Won' and whose Amount is greater than $10,000, and Cases with Status set to either 'New' or 'Escalated'. For each Account, I want to see the Account's Id, Name, CreatedDate, LastModifiedDate, BillingCity, BillingState, and BillingCountry fields, as well as the names, email addresses, and phone numbers of up to 10 Contacts ordered by name in descending order, up to 5 Opportunities ordered by CloseDate in descending order, and up to 20 Cases ordered by CreatedDate in ascending order. By having access to this comprehensive view of our Accounts and their related records, I can more easily identify trends and opportunities, as well as potential areas for improvement in our sales and support processes.
SOQL
SELECT Id, Name, CreatedDate, LastModifiedDate, BillingCity, BillingState, BillingCountry,
(SELECT Id, Name, Email, Phone FROM Contacts WHERE MailingPostalCode = '94105' ORDER BY Name DESC LIMIT 10),
(SELECT Id, Name, CloseDate, StageName, Amount FROM Opportunities WHERE StageName = 'Closed Won' AND Amount > 10000 ORDER BY CloseDate DESC LIMIT 5),
(SELECT Id, CaseNumber, Status, Description, Priority FROM Cases WHERE Status = 'New' OR Status = 'Escalated' ORDER BY CreatedDate ASC LIMIT 20)
FROM Account
WHERE BillingCountry = 'United States' AND (BillingState = 'CA' OR BillingState = 'NY')
ORDER BY CreatedDate DESC
LIMIT 50
