Window Functions: Seeing Through the Data

Regular aggregate functions like SUM() or COUNT() collapse multiple rows into a single result. You lose the individual row details.

Window Functions are different. They allow you to perform calculations across a set of rows related to the current row, without collapsing them.

Imagine building a financial dashboard that shows a customer’s daily transactions and their running account balance side-by-side. If you use a standard GROUP BY, you lose the individual daily transactions. Window functions let you keep the individual rows (the daily transactions) while computing an aggregate alongside them (the running total).

[!TIP] Think of it this way:

GROUP BY: Collapses the party into one loud Summary.

WINDOW FUNCTION: Lets everyone stay at the party but whispers secrets about their neighbors to them.

1. Anatomy of a Window Function

The syntax always involves the OVER clause.

SELECT
    product_name,
    price,
    AVG(price) OVER (PARTITION BY category_id) as avg_category_price
FROM products;

PARTITION BY: Divides rows into groups (like GROUP BY, but just for the window).
ORDER BY: Sorts rows within that partition.
Frame Clause: Defines the subset of rows (e.g., “3 rows before this one”).

2. Ranking Functions

How do you find the “Top 3 highest paid employees per department”?

Function	Description	Sequence Example (10, 10, 20)
`ROW_NUMBER()`	Unique ID for each row within partition.	1, 2, 3
`RANK()`	Same rank for ties, skips numbers.	1, 1, 3
`DENSE_RANK()`	Same rank for ties, no skipping.	1, 1, 2

SELECT
    name,
    salary,
    department,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

3. LEAD and LAG

Access data from the previous or next row without a self-join. Perfect for calculating Month-over-Month growth.

LAG(col, n): Returns value from n rows before.
LEAD(col, n): Returns value from n rows after.

SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) as prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) as growth
FROM monthly_sales;

4. Window Frames (Sliding Windows)

By default, if you use ORDER BY, the window is “unbounded preceding to current row”. But you can customize it for moving averages.

-- 3-Day Moving Average
AVG(price) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

5. Interactive: Window Function Visualizer

Explore how the Window Frame changes based on the function and partition. Hover over rows to see the frame!

Query Builder

Function:

Partition By:

Order By:

SQL Explanation:

SELECT func() OVER (...)

Result Set

Hover over a row to see its Partition and Window Frame.

[!NOTE] This module explores the core principles of Window Functions, deriving solutions from first principles and hardware constraints to build world-class, production-ready expertise.

6. Summary

Window Functions calculate across rows without collapsing them.
PARTITION BY defines the window scope.
ORDER BY defines the sequence for ranking and running totals.
DENSE_RANK(): Use if you don’t want gaps in your ranking numbers.
LEAD/LAG: Use for time-series comparisons.

Window Functions