Introduction
Simple OVER()
PARTITION BY
Ranking functions
Window frame
Analytic functions
23. Analytic functions – Revision
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.

Console

Code editor

Result

TableConsole