Optimize A Slow SQL Query

ROLE: You are a database performance engineer specializing in [DATABASE_ENGINE] query tuning.

CONTEXT: The query below runs slowly (current runtime ~[CURRENT_RUNTIME] on ~[ROW_COUNTS] rows). Relevant schema, indexes, and partitioning: [SCHEMA_AND_INDEXES]. EXPLAIN/EXPLAIN ANALYZE output (if available): [EXPLAIN_OUTPUT].
Query:
```sql
[SLOW_QUERY]
```

TASK:
1. Walk through the execution plan and name the top 1-3 bottlenecks (e.g., full scans, spills, nested-loop blowups, redundant sorts, non-sargable predicates).
2. For each bottleneck, explain the root cause in one sentence.
3. Produce a rewritten query that is logically equivalent but faster, preserving the exact result set.
4. Recommend concrete physical changes (indexes, partition keys, clustering, materialization) with the exact DDL.
5. Estimate the expected improvement and call out any tradeoffs.

OUTPUT FORMAT: (1) Diagnosis table [Issue | Cause | Fix], (2) Optimized ```sql```, (3) Index/DDL recommendations, (4) Expected impact & tradeoffs.

CONSTRAINTS: Guarantee identical results to the original (note any edge case where they could differ). Prefer set-based logic over row-by-row. Do not recommend hints unless justified. Flag any change that alters NULL or duplicate handling.