Google interview question

SQL query involving joins and analytical functions