Write a SQL query to solve a complex analytical scenario involving window functions and nested subqueries.