Medium & Hard SOQL & SOSL Practice Problems

Problem Statement

Write a SOQL query to find the Nth highest Salary__c from a custom object Employee__c. Return the full employee record at that salary rank.

Handle ties by counting distinct salary values.

Example

Salaries: [90, 85, 85, 70, 60]
N = 2 → employees with salary 85
N = 3 → employees with salary 70

Problem Statement

Build a reusable utility method that constructs and executes a dynamic SOQL query from parameters, following all Salesforce security best practices.

Method Signature

public static List<SObject> buildAndRun(
    String objectType,
    List<String> fields,
    String whereClause,
    Integer limitCount
)

Security Requirements

• Use String.escapeSingleQuotes() on the where clause to prevent SOQL injection
• Always include a LIMIT clause (default to 200 if null)
• Use String.join() to concatenate field names
• Call Database.query() to execute

Problem Statement

Write a static method that returns all Account records along with their related Contact records in a single SOQL query using a child relationship subquery in the SELECT clause.

Method Signature

public static List<Account> getAccountsWithContacts()

Key Concept — Child Subquery

A child relationship subquery sits inside the outer SELECT clause in parentheses. You use the relationship name (the plural form of the child object, for a standard-object relationship) rather than the object name itself.

SELECT Id, Name, (SELECT Id, SomeField FROM ChildRelationshipName)
FROM ParentObject

Constraints

• Use a single SOQL query — no loops, no separate Contact query
• Include at least Id and LastName in the subquery SELECT
• Order results by Account.Name ASC
• No ORDER BY or LIMIT inside the child subquery (not allowed)

Problem Statement

Write a method that returns all Contact records whose parent Account has at least one Closed Won Opportunity — using a SOQL semi-join (IN with a subquery).

Method Signature

public static List<Contact> getContactsAtWinningAccounts()

Key Concept — Semi-Join

A semi-join filters the outer query by checking whether a relationship field appears in the result of a subquery:

WHERE SomeField IN (SELECT ParentField FROM ChildObject WHERE ...)

The subquery must select a single field that matches the type of the outer WHERE field.

Constraints

• Use a single SOQL statement — no Apex loops to compare records
• The subquery must query Opportunity and filter on StageName
• Return at least Id, FirstName, LastName, AccountId

Problem Statement

Write a method that returns all Account records that have no open Cases — using a SOQL anti-join (NOT IN with a subquery).

Method Signature

public static List<Account> getAccountsWithNoOpenCases()

Key Concept — Anti-Join

An anti-join excludes records whose Id appears in a subquery result:

WHERE Id NOT IN (SELECT ParentField FROM ChildObject WHERE ...)

Constraints

• Use NOT IN with a subquery — not a loop-based approach
• Subquery must filter for open (non-Closed) Cases
• Return at least Id and Name
• Order results by Name ASC

Problem Statement

Write a method that uses a SOQL aggregate query to find all Account IDs that have more than 2 related Contacts.

Method Signature

public static List<AggregateResult> getHighContactAccounts()

Key Concept — HAVING

HAVING filters after aggregation — it is the equivalent of WHERE for aggregate results. You cannot use WHERE to filter on a COUNT() result.

SELECT GroupField, COUNT(Id) alias
FROM ChildObject
GROUP BY GroupField
HAVING COUNT(Id) > someThreshold

Constraints

• Use GROUP BY AccountId — do not loop over contacts in Apex
• Use HAVING COUNT(Id) > 2 to filter at the database level
• Order by COUNT(Id) DESC to show most-contacted accounts first
• Access results with: (Integer) result.get('cnt')

Problem Statement

Write a SOQL aggregate query to find the total number of Opportunities for each Account, returning the highest-count accounts first.

Requirements

• SELECT AccountId, COUNT(Id) oppCount
• FROM Opportunity
• GROUP BY AccountId
• ORDER BY COUNT(Id) DESC
• Return type: List<AggregateResult>

Access Results

for (AggregateResult ar : results) {
    Id accId     = (Id)      ar.get('AccountId');
    Integer cnt  = (Integer) ar.get('oppCount');
}

Problem Statement

Retrieve the names and emails of all Contacts whose Account has an Opportunity matching a given opportunity name.

Use a semi-join to find relevant AccountIds from the Opportunity object, then return Contacts for those accounts.

Requirements

• Return each matching Contact's Id, Name, and Email.
• Use a semi-join against Opportunity to find the qualifying Accounts — no Apex loops.
• Method parameter: String opportunityName

Problem Statement

Retrieve Account names that have at least one associated Opportunity with an Amount greater than $50,000.

Use a semi-join to avoid duplicate Account rows.

Requirements

• Return each qualifying Account's Id, Name, and Industry.
• Use a semi-join against Opportunity to find Accounts with a large enough deal — no Apex loops.
• Sort the result alphabetically by Account Name.

Problem Statement

Retrieve the names and email addresses of all Contacts whose Account has at least one Opportunity in the "Proposal/Price Quote" stage.

Approach

Use a semi-join: filter Contacts by AccountIds that have a Proposal-stage Opportunity.

Requirements

• Return each qualifying Contact's Id, Name, and Email.
• Use a semi-join against Opportunity to find the qualifying Accounts — no Apex loops.

Problem Statement

Find and return the AccountId of the Account that has the highest number of associated Opportunities.

Requirements

• SELECT AccountId, COUNT(Id) cnt
• GROUP BY AccountId → ORDER BY COUNT(Id) DESC → LIMIT 1
• Return the AccountId from the first (and only) result row
• Return null if no Opportunities exist

Problem Statement

Retrieve all Accounts that have both at least one Opportunity and at least one Case, using two semi-joins combined with AND.

Requirements

• Include only Accounts that have at least one related Opportunity.
• Also require at least one related Case on the same Account.
• Single SOQL statement — no Apex loops.

Problem Statement

Identify Opportunities that have not been modified in the last 30 days.

Key Concept

LastModifiedDate < LAST_N_DAYS:30 means the record was modified more than 30 days ago — i.e., it sits outside (before) the rolling window.

Requirements

• WHERE LastModifiedDate < LAST_N_DAYS:30
• SELECT Id, Name, StageName, LastModifiedDate
• ORDER BY LastModifiedDate ASC (oldest first)

Problem Statement

Find Contacts whose MailingState is not equal to their parent Account's BillingState — these Contacts are located in a different state than their company's billing address.

Key Concept — SOQL Cannot Compare Two Fields Directly

Unlike standard SQL, a SOQL WHERE clause can only compare a field against a literal, bind variable, or subquery — never against another field (WHERE MailingState != Account.BillingState is not valid SOQL and will not compile). The correct pattern is to query the candidate records with only null guards in the WHERE clause, then perform the actual field-to-field comparison in Apex.

Requirements

• Query Contacts selecting Id, Name, MailingState and the related Account.BillingState
• Guard the query against nulls on both MailingState and Account.BillingState so blank addresses don't produce false mismatches
• Loop over the query results in Apex and compare c.MailingState != c.Account.BillingState, collecting only the mismatches to return

Problem Statement

Retrieve each Account's total Opportunity Amount — the sum of all Opportunity Amounts grouped by Account, highest total first.

Requirements

• SELECT AccountId, SUM(Amount) totalAmount
• FROM Opportunity
• GROUP BY AccountId
• ORDER BY SUM(Amount) DESC

Access

Decimal total = (Decimal) ar.get('totalAmount');

Problem Statement

Retrieve the names and emails of all Contacts whose Account has at least one Opportunity with an Amount greater than the average Opportunity Amount.

3-Step Approach

1. Compute the average Opportunity Amount using an aggregate query.
2. Collect the AccountIds of Opportunities whose Amount exceeds that average.
3. Fetch the Name and Email of Contacts belonging to those Accounts.

Governor Rules

• No SOQL inside loops — collect IDs first, then query
• Maximum 3 SOQL queries total

Problem Statement

Retrieve the top 3 Opportunities by Amount for each Account, returned as a Map<Id, List<Opportunity>> keyed by AccountId.

Why Apex?

SOQL does not support LIMIT n PER GROUP, so post-query Apex logic is needed to cap each account's list at 3.

Requirements

• One SOQL query: ORDER BY Amount DESC
• Iterate and stop adding once a group reaches 3 entries
• Return Map<Id, List<Opportunity>>

Problem Statement

Identify Opportunities where the custom field Approval_Status__c is set to "Pending".

Custom Field Convention

All custom fields in Salesforce end with __c. Reference them exactly as defined — casing and underscores matter.

Requirements

• Filter Opportunities to those whose Approval_Status__c equals "Pending"
• Include Approval_Status__c in the selected fields
• Sort the results by Amount, highest first

Problem Statement

Find all Accounts that have not been modified in the last 60 days. These are candidates for re-engagement or data cleanup.

Key Concept

LastModifiedDate < LAST_N_DAYS:60 returns records whose last modification is before the 60-day rolling window (older than 60 days).

Requirements

• WHERE LastModifiedDate < LAST_N_DAYS:60
• ORDER BY LastModifiedDate ASC (oldest first)

Problem Statement

Retrieve all Closed Won Opportunities whose CloseDate falls within the last calendar quarter.

Key Concept

LAST_QUARTER covers the complete previous fiscal quarter (Q1, Q2, Q3, or Q4) relative to today.

Requirements

• WHERE StageName = 'Closed Won' AND CloseDate = LAST_QUARTER
• ORDER BY Amount DESC

Problem Statement

Write a SOQL aggregate query that returns each Account's total Contact count, ordered from most contacts to fewest.

Requirements

• SELECT AccountId, COUNT(Id) contactCount
• FROM Contact — not Account
• GROUP BY AccountId
• ORDER BY COUNT(Id) DESC

Problem Statement

Retrieve Opportunities where the custom field Priority__c is "High" and the Amount exceeds $50,000.

Requirements

• Filter to Opportunities where Priority__c is "High" and Amount exceeds 50,000
• Include Priority__c among the selected fields
• Sort the results by Amount, highest first

Problem Statement

Retrieve the names of all Accounts that have at least one Opportunity in the "Prospecting" stage, using a semi-join to avoid duplicate rows.

Requirements

• Return each qualifying Account's Id, Name, and Industry.
• Use a semi-join against Opportunity, filtered to the "Prospecting" stage.
• Sort the result alphabetically by Name.

Problem Statement

Write a SOQL query that traverses two levels of parent relationships: Opportunity → Account → Owner.

Requirements

• Return each Opportunity's Id, Name, StageName, and Amount, plus its parent Account's Name and the Account Owner's Name.
• Only include Opportunities that are still open.
• Sort by the parent Account's Name, and cap the result at 20 rows.

Key Concept

SOQL supports up to 5 levels of parent traversal using chained dot notation: Account.Owner.Name goes Opportunity → Account → User (Owner).

Problem Statement

Write a child-to-parent query that filters Contacts by their parent Account's Type field.

Requirements

• Return each Contact's Id, FirstName, LastName, Email, and Phone, plus their parent Account's Name and Type.
• Only include Contacts whose Account's Type is "Customer".
• Sort by LastName, and cap the result at 50 rows.

Problem Statement

Write a SOQL query that retrieves Cases with fields from two different parent objects: Contact and Account.

Requirements

• Return each Case's Id, CaseNumber, Subject, and Status, plus its Contact's first/last name and email, and its Account's name and phone.
• Only include Cases that are not yet closed.
• Sort by CaseNumber, and cap the result at 20 rows.

Problem Statement

Write a query that returns open Opportunities where the parent Account's AnnualRevenue meets a minimum threshold.

Requirements

• Return each Opportunity's Id, Name, StageName, Amount, and CloseDate, plus its parent Account's Name and AnnualRevenue.
• Only include still-open Opportunities whose parent Account's AnnualRevenue meets a $1,000,000 minimum threshold.
• Sort by the parent Account's AnnualRevenue, largest first, and cap the result at 20 rows.

Problem Statement

Build a contact directory query that retrieves multiple Account fields alongside contact details for a richer data set.

Requirements

• Return each Contact's Id, FirstName, LastName, Email, Phone, and Title, along with the parent Account's Name, Type, BillingCity, and Website.
• Only include contacts whose parent Account has a Name populated.
• Sort primarily by the Account's Name, then by the contact's LastName, and cap the results at 50 rows.

Problem Statement

Write a query with two-level parent traversal: Contact → Account → Owner (User), retrieving the account owner's name and email.

Requirements

• Return each Contact's Id, FirstName, LastName, and Email, plus the parent Account's Name and the Account owner's Name and Email.
• Only include contacts whose account owner has a name populated.
• Sort primarily by the account owner's name, then by the contact's LastName, and cap the results at 30 rows.

Problem Statement

Retrieve Accounts that have open Cases, using a parent-to-child subquery for the cases and a semi-join to filter only accounts with open cases.

Requirements

• For each qualifying Account, return its Id, Name, and Phone, plus a nested list of its non-closed cases' Id, CaseNumber, Subject, Status, and Priority, sorted by priority.
• Restrict the outer Accounts to only those that actually have at least one non-closed case, using a filter against the Case object.
• Sort the outer results alphabetically by Name and cap them at 10 rows.

Problem Statement

Write a parent-to-child query that retrieves Accounts with only the Contact Id in the subquery — the minimal useful subquery pattern.

Requirements

• Return each qualifying Account's Id, Name, and Type, plus just the Id of each related contact in a nested list.
• Restrict the results to Accounts that actually have at least one related contact.
• Sort alphabetically by Name and cap the results at 20 rows.

Problem Statement

Retrieve Accounts with a subquery for Contacts that have an email address. Filter and sort within the subquery.

Requirements

• Return each Account's Id, Name, and Industry, plus a nested list of its contacts that have an email address on file, showing each contact's Id, FirstName, LastName, and Email.
• Sort the nested contacts alphabetically by LastName.
• Sort the outer Account results alphabetically by Name and cap them at 15 rows.

Problem Statement

Retrieve high-revenue Accounts with their Opportunities sorted by close date. Combines outer-query filtering with inner-subquery sorting.

Requirements

• Return each Account's Id, Name, and AnnualRevenue, plus a nested list of its opportunities' Id, Name, StageName, Amount, and CloseDate, sorted by close date (soonest first).
• Restrict the outer results to Accounts with a positive annual revenue.
• Sort the outer results by AnnualRevenue from highest to lowest, and cap them at 10 rows.

Problem Statement

Retrieve Accounts with a Contacts subquery that sorts contacts by LastName using the NULLS LAST clause to handle contacts with no last name gracefully.

Requirements

• Return each Account's Id, Name, and Type, plus a nested list of its contacts' Id, FirstName, LastName, and Title.
• Sort the nested contacts alphabetically by LastName, placing contacts with no last name at the end of that list rather than the beginning.
• Sort the outer Account results alphabetically by Name and cap them at 15 rows.

Problem Statement

Write a query with two parent-to-child subqueries in a single SELECT, retrieving both Contacts and Opportunities for each Account.

Requirements

• Include a nested list of each Account's contacts (Id, FirstName, LastName, Email), sorted alphabetically by LastName.
• Also include a nested list of each Account's still-open opportunities (Id, Name, StageName, Amount).
• Return each Account's Id, Name, Industry, and AnnualRevenue, restricted to Accounts with a populated Name, sorted alphabetically by Name and capped at 10 rows.

Problem Statement

Write a three-level nested query: Account → Cases → CaseComments, using two levels of parent-to-child subqueries.

Requirements

• Within each case's nested list, include a further nested list of its comments (Id, CommentBody, CreatedDate), newest first.
• For each Account, include a nested list of its non-closed cases (Id, CaseNumber, Subject, Status), sorted by case number, each carrying the nested comments described above.
• Return each Account's Id and Name, sorted alphabetically by Name and capped at 5 rows.

Problem Statement

Write two SOQL queries in the same editor — one using child-to-parent, one using parent-to-child — demonstrating both relationship directions.

Query 1 — Child-to-Parent

• Return each open Opportunity's core fields along with its parent Account's Name and the account owner's Name, sorted by Amount from highest to lowest and capped at 20 rows.

Query 2 — Parent-to-Child

• Return Accounts with a nested list of their contacts' core fields (sorted by LastName), capped at 10 rows.

Problem Statement

Write a SOQL aggregate query that returns the AccountId and the number of Contacts (COUNT(Id)) for every Account that has 5 or more Contacts.

Requirements

• Query the Contact object, excluding Contacts with no Account.
• Group the results by Account so each Account appears once with its Contact count.
• Alias the count as contactCount (the grading checks this exact alias).
• Only keep Accounts whose Contact count meets the 5-or-more threshold.
• Sort so the Accounts with the most Contacts appear first.
• Limit to 20 results.

Key Concepts

• GROUP BY — collapses rows by a field value.
• HAVING — filters after grouping (unlike WHERE which filters before).
• Aggregate functions: COUNT(Id), SUM(), AVG(), MAX(), MIN().

Problem Statement

Write two SOQL queries demonstrating semi-join and anti-join patterns.

Query 1 — Semi-Join (IN subquery)

• Select the Id, FirstName, LastName, and Email of Contacts whose Account is in the Technology industry, using a semi-join rather than a dot-notation traversal.
• Sort alphabetically by LastName and cap the results at 50 rows.

Query 2 — Anti-Join (NOT IN subquery)

• Select the Id, Name, Industry, and AnnualRevenue of Accounts that have no related Contacts at all, using an anti-join.
• Sort alphabetically by Name and cap the results at 25 rows.

Key Rules

• The subquery in an anti-join must exclude records where the join field is null, otherwise the NOT IN comparison returns no rows at all.
• Semi-join / anti-join subqueries can only return one field.

Problem Statement

Write AccountSearchService with a static method searchByName(String searchTerm) that performs a dynamic SOQL search on Account using LIKE.

Requirements

• Return an empty list if searchTerm is blank.
• Sanitise input: String.escapeSingleQuotes(searchTerm.trim()).
• Wrap with wildcards: '%' + safeTerm + '%'.
• Build the SOQL string using string concatenation with a bind variable (:safeTerm).
• Execute with Database.query(soql).
• Wrap in try-catch(QueryException e) — return empty list on error.
• Return the fields Id, Name, Industry, AnnualRevenue, Phone, capped at 50 records.

Best Practices

• String.escapeSingleQuotes() is mandatory for dynamic SOQL — prevents SOQL injection attacks.
• Catch QueryException specifically, not generic Exception, for SOQL errors.
• Return an empty list (not null) on error — callers should never receive null.

Problem Statement

Write two advanced aggregate SOQL queries using date functions.

Query 1 — Monthly Revenue Breakdown

• For each calendar month, return the total revenue and count of won Opportunities that closed in that month, aliasing the aggregated columns.
• Restrict the results to won opportunities that closed within the current fiscal year.
• Group by month and sort chronologically.

Query 2 — Deal Size Stats by Stage

• For each Stage, return the average, highest, and lowest Amount among closed opportunities.
• Restrict the results to opportunities that closed within the last 90 days.
• Group by stage and sort by the average deal size, largest first.

Key Concepts

• CALENDAR_MONTH(), FISCAL_YEAR() — SOQL date functions usable in SELECT and GROUP BY.
• SOQL provides relative date literals for "current fiscal year" and "the last N days" so you don't need to compute date boundaries yourself.
• AVG(), MAX(), MIN() — aggregate functions.

Problem Statement

Implement getTotalOpportunityAmount() that returns the sum of the Amount field across all Opportunities.

Requirements

• Use SUM(Amount) with alias totalAmount
• FROM Opportunity
• Access the result with results[0].get('totalAmount')
• Cast and return as Decimal

Problem Statement

Implement getAverageOpportunityAmount() that returns the average of the Amount field across all Opportunities.

Requirements

• Use AVG(Amount) with alias avgAmount
• FROM Opportunity
• Access via results[0].get('avgAmount')
• Return type: Decimal

Problem Statement

Implement getMaxOpportunityAmount() that returns the largest Amount value across all Opportunities.

Requirements

• Use MAX(Amount) with alias maxAmount
• FROM Opportunity
• Access via results[0].get('maxAmount')
• Return type: Decimal

Problem Statement

Implement getMinOpportunityAmount() that returns the smallest Amount value across all Opportunities.

Requirements

• Use MIN(Amount) with alias minAmount
• FROM Opportunity
• Access via results[0].get('minAmount')
• Return type: Decimal

Problem Statement

Implement getOpportunityStageCount() that returns a count of Opportunities grouped by StageName.

Requirements

• SELECT StageName, COUNT(Id) totalCount
• FROM Opportunity
• GROUP BY StageName
• Return type: List<AggregateResult>

Accessing Results

for (AggregateResult ar : results) {
    String stage = (String)  ar.get('StageName');
    Integer cnt  = (Integer) ar.get('totalCount');
}

Problem Statement

Implement getStagesHavingMoreThanFiveOpps() that returns only Opportunity stages that have more than 5 records.

Requirements

• SELECT StageName, COUNT(Id) totalCount
• GROUP BY StageName
• HAVING COUNT(Id) > 5

Key Concept

HAVING filters after grouping (like a WHERE for aggregate results). It cannot be replaced by WHERE because WHERE filters individual rows, not groups.

Problem Statement

Implement getContactsWithAccountName() that retrieves all Contacts along with the parent Account's Name using a child-to-parent relationship traversal.

Requirements

• Return the Id, FirstName, and LastName of every Contact
• Also include the related Account's Name, reached by traversing the lookup from Contact to Account
• Query the Contact object

Dot Notation

Traverse up to 5 levels of parent relationships using dots: Contact.Account.Owner.Profile.Name.

Problem Statement

Implement getOppsWithAccountName() that retrieves all Opportunities along with the parent Account's Name.

Requirements

• Return the Id and Name of every Opportunity
• Also include the related Account's Name, reached by traversing the lookup to Account
• Query the Opportunity object

Problem Statement

Implement getOpportunityOwnerProfile() that traverses two levels of parent relationships to retrieve the Opportunity owner's name and their Profile name.

Requirements

• Return the Id and Name of every Opportunity
• Also include the owner's name and the owner's Profile name, reached by traversing two levels of parent relationships
• Query the Opportunity object

Relationship Chain

An Opportunity's owner is a User, and that User has a related Profile — chaining a relationship traversal across both hops lets you reach the Profile's Name from the Opportunity.

Problem Statement

Implement getAccountsWithContacts() using a parent-to-child subquery to fetch each Account along with its related Contacts in a single query.

Requirements

• Return each Account's Id and Name
• For each Account, also include a nested list of its related Contacts, each with Id, FirstName, and LastName
• Use the plural child relationship name when writing the nested query

Key Concept

The child relationship name is the plural of the child object: Contacts, Opportunities, Cases, etc.

Problem Statement

Implement getAccountsWithOpportunities() using a parent-to-child subquery to retrieve each Account with its related Opportunities.

Requirements

• Return each Account's Id and Name
• For each Account, also include a nested list of its related Opportunities, each with Id, Name, and Amount
• Note the plural child relationship name for Opportunity records

Problem Statement

Implement getAccountsWithClosedWonOpps() that fetches every Account together with only its Closed Won Opportunities using a filtered parent-to-child subquery.

Requirements

• Return every Account's Id and Name, regardless of its Opportunities
• For each Account, include a nested list of only its Opportunities that are in the "Closed Won" stage, each with Id, Name, and StageName

Problem Statement

Implement getAccountsHavingOpps() using a semi-join to return only Accounts that have at least one related Opportunity.

Requirements

• Return only the Id and Name of Accounts that have at least one related Opportunity
• Filter the outer Account query by checking membership against an inner query on the Opportunity object that returns the linking AccountId

Semi-Join vs Subquery

A semi-join uses IN (SELECT …) in the WHERE clause to filter the outer object based on existence in a related object — no JOIN keyword needed in SOQL.

Problem Statement

Implement getAccountsWithoutOpps() using an anti-join to return only Accounts that have no related Opportunities.

Requirements

• Return only the Id and Name of Accounts that have zero related Opportunities
• Filter the outer Account query by excluding any match against an inner query on the Opportunity object that returns the linking AccountId

Anti-Join vs Semi-Join

• Semi-join: IN (subquery) — records that have a match
• Anti-join: NOT IN (subquery) — records that do NOT have a match

Problem Statement

Implement getOpportunityAmountByStage() that returns the total Opportunity amount for each stage.

Requirements

• SELECT StageName, SUM(Amount) totalAmount
• FROM Opportunity
• GROUP BY StageName
• Return type: List<AggregateResult>

Accessing Results

Decimal total = (Decimal) ar.get('totalAmount');
String stage  = (String)  ar.get('StageName');

Problem Statement

Implement getDynamicAccounts(String industryName) that builds a SOQL query dynamically at runtime and executes it with Database.query().

Requirements

• Build a SOQL query string, as text, that selects the Id, Name, and Industry of Accounts filtered by the given industryName
• Use a bind variable so industryName is substituted safely, not concatenated as raw text
• Execute the built string with Database.query(queryStr)
• Return type: List<Account>

Security Note

Using :variable bind syntax in dynamic SOQL is safe. Never concatenate user input directly into the query string — that creates SOQL injection vulnerabilities.

Problem Statement

Write an Apex class OpportunityOwnerStageQuery with a method getDistinctOwnerCountByStage() that returns, for each StageName, the number of distinct Opportunity Owners who have an Opportunity in that stage.

Requirements

• Use COUNT_DISTINCT(OwnerId) — a plain COUNT(OwnerId) would count every Opportunity, not every unique owner.
• Group the results by StageName, aliasing both the grouped field and the aggregate.
• Return type is List<AggregateResult>.

Best Practices

• COUNT_DISTINCT vs plain COUNT is a common SOQL gotcha — pick the one that actually answers the business question ("how many opportunities" vs "how many different owners").
• Alias both the grouped field and the aggregate expression so calling code can read results by name instead of positional index.
• Order grouped results deterministically (ORDER BY StageName ASC) so output doesn't depend on database internals.

Problem Statement

Write an Apex class StageTypeRevenueQuery with a method getHighRevenueStageTypeCombos() that groups Opportunities by both StageName and Type, totals the Amount for each combination, and returns only combinations whose total exceeds a revenue threshold.

Requirements

• Group by both StageName and Type together — two grouping fields, not one.
• Keep only combinations whose summed Amount exceeds the threshold, where the threshold is a named constant, not a raw literal in the query.
• Order the result by total descending, so the highest-revenue combination comes first.

Best Practices

• Grouping by more than one field is straightforward — just list every grouping column, separated by commas, in both SELECT and GROUP BY.
• HAVING filters on the aggregated value; a plain WHERE cannot reference SUM(Amount).
• A business-meaningful threshold like this belongs in a named constant (REVENUE_THRESHOLD) rather than a bare number, even inside a query.

Problem Statement

Write an Apex class UnmatchedLeadQuery with a method getLeadsWithoutMatchingContact() that returns every non-converted Lead whose Email does not match any existing Contact's Email.

Requirements

• Use an anti-join (NOT IN with a subquery) against Contact's Email field — not an Apex loop.
• Filter out Leads with a null Email, and filter the inner subquery to Email != null Contacts too.
• Only consider non-converted Leads (IsConverted = false).

Best Practices — the NOT IN / NULL pitfall

• In SQL-family languages (SOQL included), X NOT IN (subquery) that returns even one null makes the whole comparison evaluate to unknown/false for every row — the outer query would silently return nothing. Filtering the subquery to Email != null avoids this trap entirely.
• Guarding the outer Email != null too avoids comparing a null Lead email against the subquery at all.
• WITH SECURITY_ENFORCED plus LIMIT round out the query as production-safe.

Problem Statement

Write an Apex class CasePriorityCountQuery with a method getCaseCountByPriority(Id accountId) that returns, for a single Account, how many Cases exist at each Priority level.

Requirements

• Scope the aggregation to a single Account, identified by the accountId parameter, before grouping.
• Group the results by Priority, aliasing both the grouped field and the count.

Best Practices

• Filtering with WHERE before an aggregate GROUP BY scopes the aggregation to exactly the records you care about — the WHERE clause applies before grouping, not after.
• Alias both the grouped dimension and the aggregate so results read naturally by name.

Problem Statement

Write an Apex class ContactAccountRevenueQuery with a method getContactsWithAccountRevenue() that returns every Contact together with its parent Account's Name and AnnualRevenue, using a child-to-parent relationship query.

Requirements

• Traverse the relationship with dot notation: Account.Name, Account.AnnualRevenue — no second query needed.
• Only include Contacts that actually have a parent Account.

Best Practices

• A single child-to-parent dot-notation query avoids an unnecessary second SOQL query and a manual map-join — the platform resolves the relationship for you.
• Excluding Contacts with no parent Account guards against every Account.* field coming back null on those rows.

Problem Statement

Write an Apex class AccountTopOpportunityQuery with a method getAccountsWithTopOpenOpportunity() that returns every Account together with (at most) its single highest-value open Opportunity, using a parent-to-child subquery.

Requirements

• The subquery must consider only Opportunities that are still open, and narrow that set down to just the single one with the largest Amount per Account.
• Return type is List<Account> — each Account's related list will contain zero or one Opportunity.

Best Practices

• A subquery can carry its own independent WHERE, ORDER BY, and LIMIT — this is the standard way to fetch "top N related records per parent" without a second query or in-memory sorting.
• This single query replaces what would otherwise require querying Accounts, then looping to query each Account's Opportunities separately — exactly the kind of query-in-a-loop this pattern avoids.

Problem Statement

Write an Apex class ContactAccountOwnerQuery with a method getContactsWithAccountOwner() that returns every Contact together with the Name and Email of its parent Account's Owner — a two-level child-to-parent traversal (Contact → Account → Owner).

Requirements

• Use a two-level dot-notation path: Account.Owner.Name, Account.Owner.Email.
• Guard against Contacts with no parent Account.

Best Practices

• SOQL supports traversing up to 5 levels of parent relationships via dot notation — Account.Owner.Name is a two-level hop (Contact → Account → Owner), all resolved in a single query with no additional round trips.
• The same null-Account guard matters even more here, since a null Account also means a null Owner two levels up.

Problem Statement

Write an Apex class OpenOpportunityGapsQuery with a method getOpenOpportunitiesMissingNextStep() that returns every open Opportunity that has no NextStep defined — a common "sales hygiene" report.

Requirements

• Only open Opportunities should be included.
• Only those with a blank/null NextStep.

Best Practices

• In SOQL, comparing a field to null is written as Field = null (or != null) — this is the SOQL equivalent of Apex's String.isBlank() check, since SOQL doesn't have a text-comparison function for blank/whitespace-only text fields.
• Combining the open-status check with the blank check in one query avoids a wasteful "fetch everything then filter in Apex" pattern.

Problem Statement

Write an Apex class MultiOpenOpportunityAccountsQuery with a method getAccountsWithMultipleOpenOpportunities() that returns each Account's open Opportunity count, but only for Accounts with more than one open Opportunity — ranked from most to fewest.

Requirements

• Filter to open Opportunities with WHERE IsClosed = false before grouping.
• GROUP BY AccountId, then use HAVING COUNT(Id) > 1 to keep only multi-opportunity Accounts.
• Order by the count, descending.

Best Practices — WHERE vs. HAVING

• WHERE filters individual rows before they're grouped; HAVING filters groups after aggregation. You cannot write WHERE COUNT(Id) > 1 — the aggregate doesn't exist yet at the row-filtering stage, which is exactly why HAVING exists as a separate clause.
• Ordering by the same aggregate expression used in HAVING keeps the most significant Accounts at the top of the result.

Problem Statement

Write an Apex class ActiveAccountsWithContactsQuery with a method getActiveAccountsWithContacts() that returns every Account that both has at least one open Opportunity and has at least one Contact — along with the list of those Contacts.

Requirements

• Use a semi-join against Opportunity to require at least one open Opportunity.
• Use a second, separate semi-join against Contact to require at least one Contact.
• Also include each qualifying Account's Contacts via a parent-to-child subquery in the SELECT list.

Best Practices

• Two semi-joins combined with AND is how SOQL expresses "must have a related record satisfying condition A and a related record satisfying condition B" without pulling everything into Apex and filtering there.
• The parent-to-child subquery that shapes the returned Contacts data is independent from the semi-join subqueries in the WHERE clause — one shapes the returned data, the others just filter which Accounts qualify.

Business Scenario

This org has multi-currency enabled — Closed Won Opportunities are recorded in whatever currency the deal was closed in. Finance needs one combined total in USD across every currency.

Requirements

• Write an Apex class MultiCurrencyRevenueQuery with a method getTotalWonRevenueInUSD(Map<String, Decimal> rateToUsdByCurrencyCode) taking a currency-code-to-USD-conversion-rate map (e.g. {'EUR' => 1.08}).
• Aggregate Closed Won Amount, grouped by CurrencyIsoCode, in a single query.
• Convert each currency's subtotal to USD using the supplied rate map, and sum the results.
• If a currency code isn't in the rate map, treat its rate as 1 (a safe fallback) rather than throwing.

Best Practices

• Aggregating separately per currency is the standard way to handle amounts in a multi-currency org — summing raw numbers across different currencies would silently treat them as if they were the same unit.
• Currency conversion belongs in Apex (using externally-supplied, up-to-date rates), not hardcoded — this method accepts the rate map as a parameter rather than hardcoding rates.