When window functions are evaluated
Window functions and GROUP BY
Summary

Instruction

Okay. As you can see, the query did not succeed. So, how can we select some information for those auctions which had their final_price higher than the average final_price? We have to use a subquery. Take a look:

SELECT
  id,
  final_price 
FROM
    (SELECT
      id,
      final_price,
      AVG(final_price) OVER() AS avg_final_price
      FROM auction) c
WHERE final_price > avg_final_price

In the FROM clause, we introduced a subquery where we selected both the final_price for each auction and the average final_price. Because the whole subquery is calculated before the external query, we can use avg_final_price in the external query.

Exercise

Find the id, country and views for those 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.

Console

Code editor

Result

TableConsole