SQL - Window Functions

Window Functions

Window functions perform calculations across rows related to the current row while preserving the original row-level output.

This differs from GROUP BY (see SQL Filtering And Aggregation), which collapses rows into groups. Window functions add group-level or ordered calculations beside each original row, which is why they are often the cleanest tool for running totals, rolling averages, and “rank within group” problems. They also compose naturally inside CTEs when a query needs to filter on the result of the window calculation.

Basic Syntax

SELECT
    column1,
    aggregate_function(column2) OVER (
        PARTITION BY partition_column
        ORDER BY order_column
        ROWS BETWEEN frame_start AND frame_end
    ) AS result
FROM table_name;

PARTITION BY

PARTITION BY divides rows into groups and performs the window calculation within each group.

SELECT
    product_id,
    sale_date,
    amount,
    SUM(amount) OVER (PARTITION BY product_id) AS total_per_product
FROM sales;

If OVER() is empty, the entire dataset is treated as one partition.

PARTITION BY Vs GROUP BY

Aspect	PARTITION BY	GROUP BY
Rows returned	original rows preserved	reduced to groups
Use case	add aggregate beside detail	summarize data
Example	running total per group	total per group only

ORDER BY In Window Functions

ORDER BY defines row order inside each partition.

SELECT
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

Window Frames

ROWS BETWEEN defines which rows are included in the calculation.

SELECT
    date,
    tweets,
    AVG(tweets) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3day_avg
FROM twitter_data;

Common frame boundaries:

• UNBOUNDED PRECEDING: from start of partition
• UNBOUNDED FOLLOWING: to end of partition
• n PRECEDING: n rows before current
• n FOLLOWING: n rows after current
• CURRENT ROW: current row only

Ranking Functions

RANK() produces rank with gaps when there are ties.

RANK() OVER (ORDER BY score DESC)

DENSE_RANK() produces rank without gaps.

DENSE_RANK() OVER (ORDER BY score DESC)

ROW_NUMBER() assigns a unique sequential number to each row.

ROW_NUMBER() OVER (ORDER BY score DESC)

Reference

SQL Aggregate Window Functions | DataLemur