Grab interview question

SQL question, with CTE implementation. Joins