Can you explain what window functions are in SQL?

Window functions in SQL are functions that allow you to perform calculations across a set of table rows that are related to the current row, without grouping the results into a single row, as aggregate functions do. They are useful for calculating moving sums, ranking, calculating cumulative values, etc.

An example of a window function is ROW_NUMBER(), which numbers the rows within a specified window:

SELECT
  employee_id,
  department_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;

Here, for each department (partition), the rows are numbered in descending order of salary, but all rows remain in the result.