ST Engineering Interview Question

SQL subquerying on a few different table, ranking.