Walk me through writing a complex SOQL query with multiple joins, aggregations, and bind variables.

A real-world scenario: "find all open Opportunities owned by users on a list of teams, where the Account has at least one open Case, grouped by Stage with counts."

apex List<AggregateResult> results = [ SELECT StageName stage, COUNT(Id) cnt FROM Opportunity WHERE OwnerId IN (SELECT UserId FROM Team_Membership__c WHERE Team__c IN :teamIds) AND IsClosed = false AND CreatedDate >= :sinceDate AND AccountId IN (SELECT AccountId FROM Case WHERE IsClosed = false) GROUP BY StageName ORDER BY COUNT(Id) DESC LIMIT 50 ];

Key features:

1. Bind variables (:teamIds, :sinceDate) — Apex variables prefixed with :. Parameterised, type-safe, injection-protected.
2. Subquery in IN (semi-join) — Salesforce SOQL's IN-subquery. One level deep only.
3. Date arithmetic — Apex Date binds to SOQL date.
4. Aggregate — COUNT(Id) with GROUP BY. Aliases for AggregateResult access.
5. ORDER BY on aggregate.

Dynamic SOQL when runtime-built: build the WHERE clause as a string, use Database.query(query). Always use bind variables (:recId) or String.escapeSingleQuotes() on raw user input.

SOQL Injection protection: static SOQL with bind variables = automatic protection; dynamic SOQL with concatenation = vulnerable. Always parameterise.

Performance gotchas: non-selective subqueries against large tables are slow; multiple subqueries add load; aggregate row limit is 2000.