-- Finne snittranking fra 2000 til n?
SELECT prodyear, avg(rank) AS avg_rating
FROM film JOIN filmrating USING (filmid)
WHERE prodyear >= 2000
GROUP BY prodyear
ORDER BY prodyear DESC
;

SELECT prodyear, round(avg(rank)::numeric, 2) AS avg_rating
FROM film JOIN filmrating USING (filmid)
WHERE prodyear >= 2000
GROUP BY prodyear
ORDER BY prodyear DESC
;

-- Finne h?yest rankede film i 2004 

SELECT title, prodyear, MAX(rank) as rating
FROM film JOIN filmrating USING (filmid)
WHERE prodyear = 2004 AND votes > 10000
GROUP BY title, prodyear
ORDER BY MAX(rank) DESC
LIMIT 1;

-- Finne h?yest rankede filmer per ?r?