Alright! Do you remember queries that introduced
WITH? We used them to find the row with a certain rank. Now, we can find even more rows with a certain rank, each for a different group. Take a look:
WITH ranking AS (
RANK() OVER(PARTITION BY country ORDER BY rating DESC) AS rank
WHERE rank = 1;
The CTE in the parentheses creates a separate ranking of stores in each country based on their rating. In the outer query, we simply return the rows with the right rank. As a result, we'll see the best store in each country.