Amazon interview question

SQL queries based on rank functions