BNP Paribas Interview Question

Write select queries for correlated sub query