ST Engineering interview question

SQL subquerying on a few different table, ranking.