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

Good job. Before, we said that window functions were calculated after the GROUP BY clause. This has a very important implication for our queries: if the query uses any aggregates, GROUP BY or HAVING, the window function sees the group rows instead of the original table rows.

To get a better understanding of this phenomenon, take a look at the following example:

SELECT 
  category_id,
  final_price, 
  AVG(final_price) OVER() 
FROM auction;

This simple query will show the id and final_price of each auction alongside the average final_price from all the auctions. Now, take a look at the modified example with grouping:

SELECT 
  category_id,
  MAX(final_price), 
  AVG(final_price) OVER() 
FROM auction 
GROUP BY category_id;

Will this query work? Let's find out!

Exercise

Try to run the template query from the example.

As you can see, the query doesn't work. This is because we can't use the column final_price in the window function. Once the rows have been grouped, there is no final_price value that makes sense for all the rows together.