Chamberlain Group interview question

How SQL proceed with it's logic