Window Functions in SQL

Window functions allow calculations across a set of rows related to the current row, without collapsing the result set like GROUP BY. Unlike aggregate functions that return one row per group, window functions preserve all rows while computing values over a defined window. This makes them particularly useful for ranking, running totals, moving averages, and accessing previous or next values.

A window function follows this structure:

FUNCTION(column) OVER (
    PARTITION BY column1
    ORDER BY column2
    ROWS BETWEEN ...
)

The window definition consists of three components:

• PARTITION BY: Divides rows into independent partitions before applying the function.
• ORDER BY: Defines the sequence of rows within each partition, affecting calculations.
• ROWS BETWEEN or RANGE BETWEEN: Determines which rows are included in the window frame.

Understanding Execution Order

Window functions are evaluated after the FROM, WHERE, and GROUP BY clauses but before ORDER BY. This means:

• They cannot be used in WHERE because they rely on full result sets.
• They operate after aggregation but do not collapse rows like GROUP BY.
• ORDER BY in the main query does not affect ordering inside OVER().

SELECT id, salary,
       SUM(salary) OVER (PARTITION BY department ORDER BY id) AS running_total
FROM employees
ORDER BY salary DESC;

Here, the window function uses id for ordering, while the final output is sorted by salary.

Importance of ORDER BY in Window Functions

In the given example:

WITH employees_salaries AS (
    SELECT
        employee_id,
        experience,
        salary,
        SUM(salary) OVER(PARTITION BY experience ORDER BY SALARY ASC) as cumulated_salary
    FROM Candidates
    ORDER by experience DESC
),
max_possible_seniors AS (
    SELECT
        *,
        SUM(salary) OVER(ORDER by experience DESC, cumulated_salary ASC) as total
    FROM employees_salaries
    WHERE cumulated_salary <= 70000
    ORDER by experience DESC, cumulated_salary ASC
)
SELECT employee_id from max_possible_seniors

The ORDER BY experience DESC, cumulated_salary ASC is required in both the window function and the external query to ensure:

• The summation respects the experience hierarchy, prioritizing senior employees first.
• cumulated_salary is correctly aggregated in a deterministic order, preventing incorrect selections.
• The selection of employees happens in a controlled sequence where total salary remains within limits.

Using a different order sequence could lead to inconsistent results in salary accumulation, changing which employees are selected.