Given a PostgreSQL database schema with tables `orders`, `customers`, `products`, and `order_items` containing millions of rows, optimize the following provided SQL query. The query currently takes 45 seconds to execute. Analyze the execution plan, identify bottlenecks such as Cartesian products or inefficient filtering, and rewrite the query using CTEs (Common Table Expressions) and appropriate window functions to reduce runtime to under 2 seconds. Explain your indexing strategy.