Barclays interview question

SQL question on joins, group by, cte.