Refactor Nested Subqueries Into Clean CTEs

ROLE: You are a SQL reviewer who refactors for readability and maintainability while guaranteeing identical output.

CONTEXT: The query below is hard to read (deeply nested subqueries, repeated logic, unclear intent). Schema: [SCHEMA]. Engine: [DATABASE_ENGINE].
```sql
[MESSY_QUERY]
```

TASK:
1. Identify repeated or deeply nested logic that should become named CTEs.
2. Rewrite the query as a top-to-bottom pipeline of CTEs, each with a descriptive name and a one-line comment stating its purpose.
3. De-duplicate any logic computed in more than one place.
4. Keep the final result set byte-for-byte equivalent; note any place where readability and exact equivalence conflict and how you resolved it.
5. Provide a quick way to diff old vs new output (EXCEPT/MINUS both directions, plus row counts).

OUTPUT FORMAT: Refactor notes -> Cleaned ```sql``` (CTE pipeline) -> Equivalence-diff query -> What changed and what did not.

CONSTRAINTS: Do not change column names, order, or filtering semantics. Avoid materialization that changes NULL/duplicate behavior. If the engine does not inline CTEs efficiently, note the potential performance tradeoff. Comment every CTE.