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
Window functions and GROUP BY
Summary

Instruction

Great. Another thing we can do with window functions when rows are grouped are leads, lags and day-to-day deltas. Take a look:

SELECT
  ended,
  SUM(final_price) AS sum_price,
  LAG(SUM(final_price)) OVER(ORDER BY ended)
FROM auction
GROUP BY ended
ORDER BY ended

The above query shows each end date with the total price of all items sold on that day and the same total price on the previous day.

Exercise

For each end day, show the following columns:

  • ended.
  • the sum of views from auctions that ended on that day.
  • the sum of views from the previous day (name the column previous_day.
  • delta – the difference between the sum of views on that day and on the previous day (name the column delta).

Stuck? Here's a hint!

You will have to use 

LAG(SUM(views))
in the window function.