Which actor has the highest difference in ratings?

Question

I need further help with my SQL problem.

In this database on movies, ratings and actors: https://i.stack.imgur.com/qFIbC.jpg

I am required to find the actor who has the largest difference between their best and their worst rated movie.

The condition is that the ratings cannot be lower than 3! (>3) My current SQL looks as follows:

SELECT * FROM stars
JOIN ratings ON stars.movie_id = ratings.movie_id
WHERE ratings.movie_id = (
    SELECT MAX(rating) - MIN(rating) FROM ratings
    WHERE rating > 3);

My expectations were that I would get somewhat of a result in my Github terminal that I can work with to adjust my SQL query.

But I seem to have reached a dead-end and I'm not sure how to solve this solution

Answer 1

You need to GROUP BY actor to calculate everyone's rating range. Then, take the actor with the largest range. Something like this:

SELECT
    person_id,
    MAX(rating) - MIN(rating) AS rating_range
FROM
    stars
    JOIN ratings ON stars.movie_id = ratings.movie_id
WHERE
    rating > 3
GROUP BY
    person_id
ORDER BY
    2 DESC
LIMIT
    1
;