Smoothstack interview question

Asked to come up with an SQL query given a few table attributes