Introduction
PARTITION BY
Ranking functions
Window frame
Analytic functions
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.
