Advanced SQL Tutorial Part 2: WINDOW FUNCTIONS -1🪟

Photo by Cosmic Timetraveler on Unsplash

SQL Aggregate Window Functions

In simple terms, they are functions that operate by creating virtual “windows” 🪟 within the dataset.

A window function performs a calculation across a set of rows that are related to the current row, but without collapsing rows like GROUP BY does.

Think of it as:
👉 “Do an aggregation, but keep every row visible.”

They allow you to perform calculations like:

• Running totals
• Rankings
• Moving averages
• Percentiles
• Comparisons between rows

Basic Syntax

SELECT 
    column1,
    column2,
    WINDOW_FUNCTION() OVER (
        [PARTITION BY partition_expression]
        [ORDER BY sort_expression [ASC|DESC]]
        [ROWS|RANGE frame_specification]
    ) AS result_column
FROM table_name;

PARTITION BY → groups rows (like mini GROUP BY).

ORDER BY → defines order within each partition.

ROWS BETWEEN … → defines the “window frame” (range of rows to look at).

Difference between GROUP BY and window functions