Window Functions

Window functions operate on a set of rows (a "window") and return a single value for each row from the underlying query. This is different from aggregate functions, which return a single value for a group of rows.

They are powerful for tasks like ranking, calculating running totals, and comparing rows to their neighbors. All window functions use the `OVER()` clause.

The `OVER()` clause has two main parts:

• `PARTITION BY`: Divides the rows into partitions (groups). The window function is applied independently to each partition. This is optional.
• `ORDER BY`: Orders rows within each partition. This is required for ranking and some other window functions.

-- Syntax
FUNCTION_NAME() OVER (
  [PARTITION BY partition_expression, ...]
  [ORDER BY sort_expression [ASC | DESC], ...]
)

These functions assign a rank to each row within a partition.

• `ROW_NUMBER()`: Assigns a unique, sequential integer to each row.
• `RANK()`: Assigns a rank, with gaps for ties. (e.g., 1, 2, 2, 4)
• `DENSE_RANK()`: Assigns a rank, with no gaps for ties. (e.g., 1, 2, 2, 3)

-- Rank employees by salary within each department
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) as row_num,
  RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rank_val,
  DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) as dense_rank_val
FROM employees;

These functions access data from a different row in the same result set without a self-join.

• `LEAD()`: Accesses data from a subsequent row.
• `LAG()`: Accesses data from a preceding row.

-- Show each employee's salary and the next employee's salary
SELECT
  name,
  salary,
  LEAD(salary, 1, 0) OVER(ORDER BY salary DESC) as next_salary,
  LAG(salary, 1, 0) OVER(ORDER BY salary DESC) as prev_salary
FROM employees;

You can use standard aggregate functions as window functions to calculate running totals, moving averages, etc.

-- Calculate a running total of salaries within each department
SELECT
  name,
  department,
  salary,
  SUM(salary) OVER(PARTITION BY department ORDER BY hire_date) as cumulative_salary
FROM employees;