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