Amazon Interview Question

One more basic SQL query which uses subquery