Scotiabank interview question

explain this sql query, basic algorithm questions