For each country, report the movie genre with the highest average ratings, and i am missing only one-CodePudding

For each country, report the movie genre with the highest average ratings, and I am missing only one step that i cant figure it out.

Here's my current code:

SELECT countries.code AS COUNTRY_CODE, mGenres.genre AS GENRE, AVG(rating) as AVERAGE_RATING,MAX(rating) AS MAXIMUM_RATING, MIN(RATING) AS MINIMUM_RATING from moviesratings
INNER JOIN moviesgenres ON moviesgenres.movieid = moviesratings.movieid
INNER JOIN users ON users.userid = moviesratings.userid
INNER JOIN countries ON countries.code = users.country
LEFT JOIN mGenres ON moviesgenres.genreid = mGenres.code
GROUP BY  mGenres.genre , countries.code
order by countries.code asc, avg(rating) desc,  mGenres.genre desc  ;

The results are correct but i am recieving the average rating of every genre for each country. And i want only the highest rating genre.

Current output:

Expected output:

CodePudding user response：

You can use the ROW_NUMBER window function to assign a unique rank to each of your rows:

partitioned by country code
ordered by descendent average rating

Once you get this ranking, you may want to select all those rows which have the highest average rating (which are the same having the ranking equal to 1).

WITH cte AS (
    SELECT c.code              AS COUNTRY_CODE, 
           mg.genre            AS GENRE, 
           AVG(rating)         AS AVERAGE_RATING,
           MAX(rating)         AS MAXIMUM_RATING, 
           MIN(RATING)         AS MINIMUM_RATING 
    FROM       moviesratings r
    INNER JOIN moviesgenres  g ON g.movieid = r.movieid
    INNER JOIN users         u ON u.userid = r.userid 
    INNER JOIN countries     c ON c.code = u.country  
    LEFT JOIN  mGenres      mg ON mg.code = g.genreid
    GROUP BY  mg.genre, 
              c.code
    ORDER BY  c.code, 
              AVG(rating) DESC,  
              mg.genre    DESC;
)
SELECT * 
FROM (SELECT *, 
             ROW_NUMBER() OVER(
                 PARTITION BY COUNTRY_CODE,
                 ORDER     BY AVERAGE_RATING) AS rn
      FROM cte) ranked_averages
WHERE rn = 1

Note: The code inside the common table expression is equivalent to yours. If you're willing to share your input tables, I may even suggest an improved query.

CodePudding user response：

You should use window function in this case by using rank() then select the first rank only.

with movies_rating (country_code, genre, average_rating, maximum_rating, minimum_rating)
as
(
    select d.code country_code,
           e.genre genre,
           avg(rating) average_rating,
           max(rating) maximum_rating,
           min(rating) minimum_rating
    from moviesratings a
             join moviesgenres b on a.movieid = b.movieid
             join users c on a.userid = c.userid
             join countries d on c.country = d.code
             left join mGenres e on b.genreid = e.code
    group by d.country_code, e.genre
),
highest_rating_per_country (rank, country_code, genre, average_rating, maximum_rating, minimum_rating)
as
(
    select rank() over (partition by country_code order by average_rating desc) rank,
           country code,
           genre,
           average_rating,
           maximum_rating,
           minimum_rating
    from movies_rating
)
select *
from highest_rating_per_country
where rank = 1

Reference:

OVER Clause