I have a slow-running SQL query involving three nested joins and a subquery. Analyze the query plan, suggest how to use indexes effectively, and rewrite the query using CTEs (Common Table Expressions) for better readability and performance.