Evaluation Order and Common Problems
Window functions and GROUP BY
10. What Window Functions See - Part 1
Summary and Review

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, such as 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
  CategoryId,
  FinalPrice,
  AVG(FinalPrice) OVER() AS AvgFinalPrice
FROM Auction;

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

SELECT
  CategoryId,
  MAX(FinalPrice) AS MaxFinalPrice,
  AVG(FinalPrice) OVER() AS AvgFinalPrice
FROM Auction
GROUP BY CategoryId;

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 FinalPrice in the window function. Once the rows have been grouped, there is no FinalPrice value that makes sense for all the rows together.