Window Functions

Window functions provide the ability to perform calculations across a set of rows that are related to the current row.

Unlike aggregate functions (which collapse rows into a single output row), window functions do not cause rows to become grouped into a single output row. The rows retain their separate identities, and the window function result is appended as an additional column.

The OVER() Clause

A window function is defined by the OVER() clause. If OVER() is empty, the "window" of rows is the entire result set.

SELECT 
    name, 
    salary, 
    AVG(salary) OVER() as company_avg_salary
FROM employees;

(This returns every employee, alongside the average salary of the entire company in a new column).

PARTITION BY

To calculate values over specific groups (without collapsing the rows), you use PARTITION BY inside the OVER() clause. It works similarly to GROUP BY, but for window functions.

SELECT 
    name, 
    department,
    salary, 
    AVG(salary) OVER(PARTITION BY department) as dept_avg_salary
FROM employees;

(This returns every employee, alongside the average salary for their specific department).

ORDER BY within OVER()

Adding an ORDER BY inside the OVER() clause changes the behavior of the window. It turns the window into a "running" window (from the start of the partition up to the current row).

SELECT 
    date, 
    daily_revenue,
    SUM(daily_revenue) OVER(ORDER BY date) as running_total
FROM sales;

Common Window Functions

In addition to aggregate functions (SUM, AVG, COUNT), several special functions are exclusively used as window functions:

• ROW_NUMBER(): Assigns a unique, sequential integer to each row within the partition.
• RANK(): Assigns a rank to each row. If there's a tie, the rank is identical, and the next rank is skipped (e.g., 1, 2, 2, 4).
• DENSE_RANK(): Similar to RANK(), but without skipping numbers (e.g., 1, 2, 2, 3).
• LEAD(): Accesses data from a subsequent row in the same result set.
• LAG(): Accesses data from a previous row in the same result set.

Example: Using LAG() for Analytics

SELECT 
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) as previous_day_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) as daily_growth
FROM daily_metrics;