SOQL Interview questions - Part 2

  SOQL Intermediate level to Advanced level - Part 2

Link to Part 1: https://sfdcinterviewpractice.blogspot.com/2025/11/soql-interview-questions.html

15: Fetch all active Accounts that have 2 or more Opportunities where StageName = 'Closed Won'

SELECT AccountId, Account.Name, COUNT(Id) FROM Opportunity WHERE Account.Active__c = 'TRUE' AND StageName = 'ClosedWon'GROUP BY AccountId, Account.Name HAVING COUNT(Id) > 1

16:Contacts who have both a Case and an Opportunity related to their Account

Find all Contacts where their parent Account has at least one Case and one Opportunity.

SELECT Id, LastName FROM Contact WHERE AccountId IN (SELECT AccountId FROM Case) AND AccountId IN (SELECT AccountId FROM Opportunity)

17: Show Accounts with both open and closed Opportunities

List Accounts that have at least one open and one closed Opportunity simultaneously.

SELECT Name FROM Account WHERE Id IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Won') AND Id IN (SELECT AccountId FROM Opportunity WHERE StageName != 'Closed Won')

18: Accounts with total Opportunity Amount > 100,000

SELECT AccountId, Account.Name, SUM(Amount) FROM Opportunity GROUP BY AccountId, Account.Name HAVING SUM(Amount) > 100000

19: Display each Opportunity Owner with total number of Opportunities and total Amount

SELECT Owner.Name, COUNT(Id), SUM(Amount) FROM Opportunity GROUP BY Owner.Name

20: Contacts who don’t belong to any Account (i.e., Private Contacts)

SELECT LastName FROM Contact WHERE AccountId = NULL

21: Opportunities linked to a Lead (via Converted OpportunityId)

List all Leads that were converted, along with their corresponding Opportunity names.

Field is ConvertedOpportunityId, but it’s only populated when:

• The Lead is converted, and

• An Opportunity was created during conversion.

SELECT Id, Name, ConvertedOpportunityId, ConvertedOpportunity.Name FROM Lead

WHERE IsConverted = TRUE AND ConvertedOpportunityId != NULL

Tip: You might not see the field in the object manager if your org hides system fields, but it’s available for query.

22: Show all Accounts where any related Opportunity’s StageName = 'Closed Lost' and Amount > 50,000

SELECT AccountId, Account.Name FROM Opportunity WHERE StageName = 'Closed Lost' AND Amount > 50000 GROUP BY AccountId, Account.Name

23: Show the number of Opportunities per Stage per Account

SELECT COUNT(Id), StageName, Account.Name FROM Opportunity GROUP BY StageName, Account.Name