0

The challenge is apparently straightforward: write an SQL query to list the names of all people who have directed a movie that received a rating of at least 9.0.

To get from names (which lives in the 'people' table) to rating (residing in the 'ratings' table), I have to go from 'people', to the table called 'directors', to 'movies', and finally to 'ratings'. To me, the simplest way to do this is using short series of INNER JOINs, like this:

SELECT people.name
FROM people
INNER JOIN directors ON people.id = directors.person_id /* keep only people also directors */
INNER JOIN movies ON directors.movie_id = movies.id /* add movies of directors */
INNER JOIN ratings ON ratings.movie_id = ratings.movie_id /* add rating of movies */
WHERE ratings.rating >= 9.0 AND (people.id IS NOT NULL) /* keep only rating above 9 and only directors with non-NULL ids  */
GROUP BY directors.person_id; /* To eliminate duplicate names where it refers to same person */

However, this bugs out, and I can't understand where I'm going wrong. If I leave out the last line (GROUP BY), it runs what seems to be an infinite loop (or just something that takes very long), and if I include the last line, it appears to just hang.

Please, Wise Ones, speak now.

1

Be greedy. In sql, that means selecting the smallest possible set of rows first. There's more than a million people rows, but fewer than 2000 ratings 9 or above.

And you may find the DISTINCT processing a useful feature.

  • So very useful. Wish I could give ten upvotes! Thanks, Dino. – RandomForestRanger Jul 10 '20 at 12:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .