Scotiabank Interview Question

explain this sql query, basic algorithm questions