Kickdrum Interview Question

A query to get the requisite information based on the given tables.