Average values of the top values for different categories

Question

I have a table with 3 fields, touristic places, the country they're in and the average rating by tourists for this place. I would like to compare different countries based on the average rating of their top touristic places. I use MySQL

It looks like this basically :

Eiffel Tower     | France  | 4,2
Trakoscan Castle | Croatia | 4,6

For example, how does the average of the notes of the 5 best touristic places in France compare with the average of the notes of the 5 best touristic places in Croatia. I know how to average all places for a country and compare that but I don't know how to combine LIMIT and GROUP BY

Thank you for your help.

Answer 1

You can use window functions to filter on the top 5 notes per country, then aggregate.

Assuming that your table has columns country, place and rating, you would phrase the query as:

select country, avg(rating) avg_rating_top5
from (
    select t.*,
        row_number() over(partition by country order by rating desc) rn
    from mytable t
) t
where rn <= 5
group by country

Note that window functions are available in MySQL 8.0 only.