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:
SELECT name,
FIRST_VALUE(name) OVER
(PARTITION BY owner_sid ORDER BY name ASC) As "First Value",
FIRST_VALUE(name) OVER
(PARTITION BY owner_sid ORDER BY name DESC) As "Last Value"
FROM sys.databases
WHERE name in ('master', 'model', 'msdb', 'tempdb')
ORDER BY owner_sid, name

The code for “Last Value” is so similar to “First Value” it might be a bit difficult for the maintenance programmer to read. A more verbose but possibly clearer approach would be to use LAST_VALUE with an explicit specification:
SELECT name,
LAST_VALUE(name) OVER
(
PARTITION BY owner_sid ORDER BY name ASC
ROWS BETWEEN UNBOUNDED PRECEDING And UNBOUNDED FOLLOWING
) As "Last Value"
FROM sys.databases
WHERE name in ('master', 'model', 'msdb', 'tempdb')
ORDER BY owner_sid, name

…and if you have a “first value” column it would be best to code it the same way. Why does LAST_VALUE sometimes give you an unexpected result? It’s because there has to be a window specification and if you don’t supply it Sql Server will default to one which starts or ends at CURRENT ROW. Now you’re not evaluating the whole set, and you may see an unexpected value.

ROWS/RANGE BETWEEN
You’ll probably use ROWS BETWEEN more than RANGE BETWEEN. The difference between the two can be seen here:
SELECT DISTINCT type_desc
,COUNT(type_desc) OVER (ORDER BY type_desc ASC ROWS BETWEEN CURRENT ROW And CURRENT ROW) As ROWS_BETWEEN
,COUNT(type_desc) OVER (ORDER BY type_desc ASC RANGE BETWEEN CURRENT ROW And CURRENT ROW) As RANGE_BETWEEN
FROM sys.objects

In the query above the ROWS_BETWEEN column returns all “1s” because Sql Server evaluates the expression physically, and in the set there is only one row between CURRENT ROW and CURRENT ROW. Remember that “Between” includes boundary elements, and in this case CURRENT ROW is at both boundaries.

The “Between” expression works the same way in the RANGE_BETWEEN column, but this time it’s operating on values. With CURRENT ROW as the maximum and minimum, what we get is a count of values in the column which equal what is in the current row.