Analyze a complex SQL query involving multiple nested subqueries, window functions, and self-joins on a table with 100 million rows. Propose an execution plan optimization strategy, suggest appropriate indexes to reduce scanning, and rewrite the query to improve performance by minimizing I/O operations.