Google Interview Question

SQL query involving joins and analytical functions