This article only deals with a couple of points so I’ll keep it short.

FIRST_VALUE, LAST_VALUE
FIRST_VALUE is a very useful ranking function and works predictably. LAST_VALUE is also useful but may give you surprises. You can ignore LAST_VALUE if you want, and just re-use FIRST_VALUE: Continue reading Notes on the OVER Clause in Sql Server Ranking Functions →