Make this slow query faster

A query that used to be fine now takes minutes.

This query is too slow. Diagnose before you rewrite.

1. Identify the likely bottleneck: full scans, a join that explodes then gets filtered, a correlated subquery running per row, functions applied to indexed columns, sorting a large intermediate result, DISTINCT hiding a join problem.
2. Say what you would need to know to confirm: row counts, cardinality, existing indexes, the plan. Ask me for it rather than guessing.
3. Give a rewritten version that returns identical results, and explain each change.
4. Rank the changes by expected speedup against effort, and tell me which ones are not worth doing.
5. Flag any rewrite that changes results in edge cases, particularly around NULLs or duplicates.

If the real fix is an index or a schema change rather than a rewrite, say that first.

Query:
Table sizes and existing indexes, if known:

Making queries faster. Diagnosing a slow query, reading the plan, and finding the smallest index that fixes it.