Barclays Interview Question

SQL question on joins, group by, cte.