SQL Query Refactoring Guide

Refactor this complex SQL query:

**Original Query:**
```sql
{{ORIGINAL_QUERY}}
```

**Query Purpose:** {{QUERY_PURPOSE}}
**Performance Issues:** {{PERFORMANCE_ISSUES}}
**Readability Concerns:** {{READABILITY_CONCERNS}}
**Database System:** {{DATABASE_SYSTEM}}

Please provide:
1. Refactored query with:
   - CTEs for clarity
   - Proper formatting
   - Meaningful aliases
   - Optimized logic
2. Step-by-step refactoring explanation
3. Performance comparison
4. Breaking into smaller queries if beneficial
5. Documentation/comments
6. Test cases to verify equivalence
7. Maintenance guidelines
8. Version for different SQL dialects