Query for Favorite Genre Actors | Dataford Interview Questions

Given tables WatchedMovies(movie_id, genre), MovieActors(movie_id, actor_id), GenreActors(genre, actor_id) and Actors(actor_id, age), write a SQL query to find the actors you would be interested in based on your favorite genre (the genre for which you have watched the most movies). Return their actor_id and age, sorted by age in descending order.

Asked in the Round 1: coding stage. Senior Data Scientist, Bengaluru.

Output

Return actor_id and age, with one row per matching actor.
Use the genre with the greatest number of watched movies. Ignore NULL genres and actors whose age is NULL.
Sort by age descending, then actor_id ascending to break ties.

Schema

WatchedMovies

Column	Type	Description
movie_idPK	INT	Identifier of a watched movie
genre	VARCHAR(50)	Movie genre

GenreActors

Column	Type	Description
genre	VARCHAR(50)	Genre associated with the actor
actor_id	INT	Identifier of an actor associated with the genre

Actors

Column	Type	Description
actor_idPK	INT	Unique actor identifier
age	INT	Actor age in years

Your question is Query for Favorite Genre Actors. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem