Pergunta de entrevista da empresa Amazon

SQL question, how to retrieve data using a join condition along with windowing features.