UC San Diego interview question

The most difficult query I had written in SQL.