Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Window Functions in PostgreSQL

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.