Introduction
PARTITION BY ORDER BY with ranking
PARTITION BY ORDER BY with window frames
PARTITION BY ORDER BY with analytical functions
Instruction
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 (
SELECT
country,
city,
RANK() OVER(PARTITION BY country ORDER BY rating DESC) AS rank
FROM store
)
SELECT
country,
city
FROM ranking
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.
Exercise
For each store, show a row with three columns: store_id, the revenue on the best day in that store in terms of the revenue and the day when that best revenue was achieved.
Stuck? Here's a hint!
Use PARTITION BY store_id ORDER BY revenue DESC.
