Accounts and Their Open Opportunities

HardSOQL 50 ptsCreated Oct 9, 2026

Solve the business requirement using SOQL.

Example

Return the matching records or aggregate groups required by the prompt.

Constraints

Use the specified SOQL features and preserve the required ordering.

Parent-to-ChildNested SubquerySemi-Join

CodeSOQL

Loading editor…

+3 hidden on submit

visible

Seed data

Account× 20 rows

Name: Account {{i}}Rating: {{pick:Hot|Warm|Cold}}OwnerId: 005000000000001AAAIndustry: {{pick:Technology|Finance|Healthcare|Retail}}AnnualRevenue: {{i*1000000}}

Contact× 80 rows

Email: {{pick:shared@example.com|contact{{i}}@example.com}}Title: {{pick:VP Sales|Chief Executive Officer|Director Sales|Engineer}}LastName: Contact {{i}}AccountId: {{id:Account:i%20+1}}FirstName: First {{i}}CreatedDate: {{datetime:i%30}}

Opportunity× 120 rows

Name: Deal {{i}}IsWon: {{pick:true|false}}Amount: {{i*50000}}OwnerId: 005000000000001AAAIsClosed: {{pick:true|false}}AccountId: {{id:Account:i%20+1}}CloseDate: {{date:-i}}StageName: {{pick:Prospecting|Proposal|Negotiation/Review|Closed Won|Closed Lost}}LeadSource: {{pick:Web|Partner|Referral}}CreatedDate: {{datetime:i%30}}

Case× 60 rows

Status: {{pick:New|Working|Closed}}Subject: Case {{i}}Priority: {{pick:High|Medium|Low}}AccountId: {{id:Account:i%20+1}}CreatedDate: {{datetime:i%30}}

Expected

10 row(s), any order

#	Id	Name	Opportunities
1	001000000000011AAA	Account 11	[6 records]
2	001000000000003AAA	Account 3	[6 records]
3	001000000000017AAA	Account 17	[6 records]
4	001000000000019AAA	Account 19	[6 records]
5	001000000000007AAA	Account 7	[6 records]
6	001000000000001AAA	Account 1	[6 records]
7	001000000000005AAA	Account 5	[6 records]
8	001000000000015AAA	Account 15	[6 records]
9	001000000000013AAA	Account 13	[6 records]
10	001000000000009AAA	Account 9	[6 records]