State Street Interview Question

Write a SQL query involving CTE, group-by operations and window functions.