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 MS SQL Server

Introduction
Simple OVER()
PARTITION BY
Ranking functions
Window Frames
Analytic Functions
23. Analytic Functions – Review and Exercise 1
PARTITION BY ORDER BY
Order of Evaluation
Finished!

Instruction

In part 6, we introduced you to the most common analytic functions: LEAD(), LAG(), FIRST_VALUE(), and LAST_VALUE(). Take a look:

SELECT
  Id,
  Rating,
  LAG(Rating) OVER(ORDER BY Id ASC) AS Lag
FROM Review;

The example above shows the Rating given with each Review and the Rating given for the previous Review.

Exercise

For each gift card, show its AmountWorth, its PaymentAmount, and two more columns showing the PaymentAmount of the first and last gift cards purchased (in terms of the PaymentDate). Name the columns FirstGiftCard and LastGiftCard.

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