Okay. Let's jump into the brackets of
OVER(...) and discuss the details. We'll start with
ROWS, because it is a bit easier to explain than
RANGE. The general syntax is as follows:
ROWS BETWEEN LowerBound AND UpperBound
BETWEEN already – it's used to define a range. So far, you've used it to define a range of values – this time, we're going to use it to define a range of rows instead. What are the two bounds? The bounds can be any of the five options:
UNBOUNDED PRECEDING – the first possible row.
n PRECEDING – the n-th row before the current row (instead of n, write the number of your choice).
CURRENT ROW – the current row only.
n FOLLOWING – the n-th row after the current row.
UNBOUNDED FOLLOWING – the last possible row.
The lower bound must come before the upper bound. In other words, a construction like:
...ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING doesn't make sense. You'll get an error from SQL Server if you run it.