Pocket Gems interview question

Given a test set of tables as an example, how do you implement the query in SQL