Vamstar interview question

SQL - Complex query, Windows function