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