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

Introduction
Simple OVER()
PARTITION BY
Ranking functions
Window frame
Analytic functions
23. Analytic functions – Summary
ORDER BY PARTITION BY
Order of evaluation
Summary

Instruction

That's it! In part 6, we introduced analytic functions: LEAD, LAG, FIRST_VALUE, LAST_VALUE, NTH_VALUE. Take a look:

SELECT
  id,
  rating,
  LAG(rating) OVER(ORDER BY id)
FROM review;

The example above shows the rating given in each review and the rating given in the previous review.

Exercise

For each giftcard, show its amount_worth, payment_amount and two more columns: the payment_amount of the first and last giftcards purchased in terms of the payment_date.

Stuck? Here's a hint!

Use both FIRST_VALUE and LAST_VALUE. Remember that LAST_VALUE requires redefining the window frame: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.