What are window functions?

The window function calculates a value for each row based on a "window" or set of rows related to the current row. It is similar to aggregate functions (SUM, AVG, COUNT), but they do not group the result into a single row for each group, instead providing an aggregated value for each individual row from the source dataset.

Main components:

1. Function: The window function itself (e.g., ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM(), AVG()).
2. OVER(): Defines that this is a window function.
3. PARTITION BY (optional): Divides the rows into partitions to which the window function is applied independently.
4. ORDER BY (mandatory in most cases): Determines the order of rows within each partition, important for functions like ROW_NUMBER, LAG, LEAD.
5. ROWS or RANGE (optional): Defines the frame of the window - a subset of rows within the partition to which the function is applied. The entire partition is often used by default.

Commonly used window functions:

• ROW_NUMBER(): Assigns a unique sequential number to each row in the partition.
• RANK(): Assigns a rank to rows within the partition. Rows with the same values receive the same rank, and the next rank skips as many numbers as there are tied rows.
• DENSE_RANK(): Similar to RANK(), but does not skip numbers.
• LAG(column, offset, default): Gets the value from the row preceding the current one by a specified offset.
• LEAD(column, offset, default): Gets the value from the row following the current one by a specified offset.
• Aggregate functions (SUM, AVG, COUNT, MIN, MAX) can be used as window functions to obtain sliding or cumulative aggregates.

Example of usage:

-- Example of using the RANK() window function to rank employees by salary within each department.
SELECT
    employee_name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank_in_department
FROM
    employees;