Pregunta de entrevista de Scotiabank

explain this sql query, basic algorithm questions