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

When window functions are evaluated
Window functions and GROUP BY
Summary

Instruction

Great. As you can see, it's fairly simple to create quite advanced statistics very easily thanks to how window functions behave with GROUP BY. Let's take a look at other use cases.

For instance, we may make a ranking based on an aggregate function. Take a look:

SELECT
  country,
  COUNT(id),
  RANK() OVER(ORDER BY COUNT(id) DESC)
FROM auction
GROUP BY country;

We grouped auctions with respect to the country, counted the number of auctions from each country... and then we created a ranking based on that count of auctions.

Exercise

Now, group the auctions based on the category. Show category_id, the sum of final prices for auctions from this category and a ranking based on that sum, with the highest sum coming first.

Stuck? Here's a hint!

Use 

RANK() OVER(ORDER BY SUM(final_price) DESC)
as your window function.