For each country, report the movie genre with the highest average rates-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 c.code AS c_CODE, menres.genre AS GENRE, AVG(RATE) as AVERAGE_rate,MAX(RATE) AS MAXIMUM_rate, MIN(RATE) AS MINIMUM_rate from movirates
leftJOIN movgenres ON movgenres.movieid = movratings.movieid
left JOIN users ON users.userid = movrates.userid
left JOIN c ON c.code = users.city
LEFT JOIN menres ON movenres.genreid = menres.code
GROUP BY  menres.genre , c.code
order by c.code asc, avg(rate) desc,  menres.genre desc  ;

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 mov_rates(c.code, genre, average, max, min)
as.
 select c.code c_code,
           e.genre genre,
           avg (rate) avg
           max (rate) max
           min (rate) min
    from movrates a
             LEFT join movge.nres b on a.movieid = b.movieid
            LEFT join users c on a.userid = c.user
           LEFT join countr.ies d on c.code = d.code
             left join mGenres e on b.genreid = e.code
    group by d.country_code, e.x
),
rategenre (rank, c_code, genre, avgrate, max, min)
as
(
    select rank() over (partition by c.c order by avgrates asc) rank,
           country code,
           genre,
           average_r.ating,
           maximum_rating,
           minimum_.ating
    from movrate \\just practicing on something
)
selec.t 2
from genre
where rank = 5

Reference:

OVER Clause