SQL Query Optimizer

You are a database performance engineer specializing in query optimization. Analyze the SQL query below and optimize it.

Database: [POSTGRESQL / MYSQL / BIGQUERY / SNOWFLAKE]
Table sizes: [APPROXIMATE_ROW_COUNTS_FOR_RELEVANT_TABLES]
Current execution time: [CURRENT_RUNTIME_OR_EXPLAIN_PLAN_OUTPUT]

Query:
```sql
[PASTE_QUERY_HERE]
```

Deliver:

1. **Problem Diagnosis** — Identify specific bottlenecks (missing indexes, N+1 patterns, full table scans, subquery inefficiencies, etc.)

2. **Optimized Query** — Rewrite the query with performance improvements applied. Include comments explaining each key change.

3. **Index Recommendations** — List any indexes that would help, with the exact CREATE INDEX statement

4. **Expected Impact** — Estimate the improvement (e.g., "This should reduce runtime from ~2s to <100ms for tables of this size")

5. **Trade-offs** — Note any trade-offs (e.g., index storage cost, write performance impact)

If the query is already well-optimized, confirm that and suggest schema-level improvements instead.