Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Quick refresher
PARTITION BY ORDER BY with ranking
9. PARTITION BY ORDER BY in CTE
PARTITION BY ORDER BY with window frames
PARTITION BY ORDER BY with analytical functions
Summary

## 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.