Calculating window functions

SQL window functions are an additional tool in your toolkit. Unlike aggregate functions, which return a single result per group of rows, window functions return a single result for each row, based on the context of that row within a window of related rows.

OVER, ORDER BY, PARTITION, and SET

Window functions have the following basic syntax:

<function> (<expression>)
OVER (
[PARTITION BY <expression_list>]
[ORDER BY <expression_list>] [ROWS|RANGE <frame specification>])

There are a few key concepts to understand here, so let’s break them down:

• The OVER keyword is what differentiates a window function from a regular function; once you see it, you know you’re in window function land. The OVER clause defines the window or subset of rows within a query result set that the window function operates on. In short, it provides a way to partition the result set into logical groups and allows the window...