Evaluation Order and Common Problems
Window functions and GROUP BY
Instruction
Okay. As you can see, the query did not succeed. So, how can we select some information for those auctions with a higher-than-average FinalPrice? We have to use a subquery. Take a look:
SELECT
Id,
FinalPrice
FROM (
SELECT
Id,
FinalPrice,
AVG(FinalPrice) OVER() AS AvgFinalPrice
FROM Auction
) C
WHERE FinalPrice > AvgFinalPrice
In the FROM clause, we introduced a subquery that selected the FinalPrice for each auction and the average FinalPrice. Because the whole subquery is calculated before the external query, we can use AvgFinalPrice in the external query.
Exercise
Find the Id, Country, and Views for auctions where the number of views was below the average.
Stuck? Here's a hint!
Create a subquery in the FROM clause with the AVG(Views) OVER() window function.
