SQL challenge: Finding top-rated films for each director

Data and Databases
1

Hey everyone, I’m working on a database project about movies and ratings. I’ve got these tables:

FilmDB(filmID int, name text, releaseYear int, filmmaker text);
Critic(criticID int, fullName text);
Review(criticID int, filmID int, score int, reviewDate date);

I need to get each filmmaker’s name, their highest-rated movie title, and that rating. Movies without a filmmaker should be ignored.

I started with this:

SELECT name, filmmaker
FROM FilmDB NATURAL JOIN Review
WHERE filmmaker IS NOT NULL

But I’m stuck on how to get the highest rating for each filmmaker. Any tips on how to finish this query? I’m pretty new to SQL, so simple explanations would be great. Thanks!

2

Hey there! have u considered using a subquery? something like:

SELECT f.filmmaker, f.name, r.avg_score
FROM FilmDB f
JOIN (
  SELECT filmID, AVG(score) as avg_score
  FROM Review
  GROUP BY filmID
) r ON f.filmID = r.filmID
WHERE f.filmmaker IS NOT NULL
AND (f.filmmaker, r.avg_score) IN (
  SELECT filmmaker, MAX(avg_score)
  FROM FilmDB f2
  JOIN (SELECT filmID, AVG(score) as avg_score FROM Review GROUP BY filmID) r2 ON f2.filmID = r2.filmID
  GROUP BY filmmaker
)

this might work? wat do u think?

3

Here’s a solution that should work for your SQL challenge:

WITH RankedFilms AS (
    SELECT 
        f.filmmaker,
        f.name AS film_title,
        AVG(r.score) AS avg_rating,
        RANK() OVER (PARTITION BY f.filmmaker ORDER BY AVG(r.score) DESC) AS rank
    FROM 
        FilmDB f
    JOIN 
        Review r ON f.filmID = r.filmID
    WHERE 
        f.filmmaker IS NOT NULL
    GROUP BY 
        f.filmmaker, f.name
)
SELECT 
    filmmaker,
    film_title,
    avg_rating
FROM 
    RankedFilms
WHERE 
    rank = 1;

This query uses a Common Table Expression (CTE) with a window function to rank each filmmaker’s movies by average rating. It then selects only the top-ranked film for each filmmaker. This approach handles ties effectively and provides the exact information you’re looking for. Let me know if you need any clarification on how this works!

4

hey whispering wind, maybe this works:

SELECT f.filmmaker, f.name, MAX(r.score) as top_rating
FROM FilmDB f
JOIN Review r ON f.filmID = r.filmID
WHERE f.filmmaker IS NOT NULL
GROUP BY f.filmmaker;

hope it helps!