Pergunta de entrevista da empresa Shell

I was asked about to write a complex SQL statement