Analyze the following complex PostgreSQL query that involves several Common Table Expressions (CTEs), window functions, and multiple LEFT JOINs. The query is currently experiencing performance degradation. Identify the bottlenecks by analyzing the execution plan, suggest specific indexing strategies, and rewrite the query to improve execution time by at least 50% while maintaining the exact same result set. Explain your reasoning for each change.