What are window functions in SQL?

Window functions are functions that perform calculations over a set of rows (a window) related to the current row and return a result for each row without grouping the data.

Main window functions:

Aggregate functions:

• SUM — calculates the total sum of values
• COUNT — counts the total number of records in a column
• AVG — calculates the arithmetic mean
• MAX — finds the maximum value
• MIN — determines the minimum value

Ranking functions:

• ROW_NUMBER: assigns a sequential number to a row within the window
• RANK: assigns a rank to a row within the window, with gaps when values tie
• DENSE_RANK: assigns a rank to a row without gaps

Offset functions:

• LAG: returns the value from the previous row
• LEAD: returns the value from the next row
• FIRST_VALUE: returns the first value in the window
• LAST_VALUE: returns the last value in the window

A detailed explanation of how window functions work can be found in our course.