Module 26 — Window Functions

1. What is a Window Function?

What it is: A window function performs a calculation across a set of rows related to the current row, without collapsing them into a single output row (unlike GROUP BY). Each row retains its identity while also getting access to aggregate-like calculations.

Why we use it: GROUP BY collapses rows — you lose individual row details. Window functions let you keep all rows while adding computed columns like rankings, running totals, and comparisons to group averages.

When we use it: When ranking rows within groups, calculating running totals, comparing each row to its group's average, accessing previous/next rows, or any "per group" calculation that needs to preserve individual rows.

-- GROUP BY collapses rows (you lose individual rows)
SELECT category, AVG(price) FROM products GROUP BY category;
 
-- Window function keeps all rows
SELECT
    name,
    category,
    price,
    AVG(price) OVER (PARTITION BY category) AS category_avg
FROM products;

2. ROW_NUMBER — Unique Sequential Number

What it is: ROW_NUMBER() assigns a unique sequential integer to each row within a partition. Unlike RANK, it never produces ties — even if values are equal, each row gets a distinct number.

Why we use it: When you need a unique identifier for ordering — "number products within each category", "paginate results with consistent ordering", or "select the top N per group".

When we use it: When implementing pagination within groups, when finding the "first" or "latest" row per group, or when you need a guaranteed unique sequence.

-- Number products within each category
SELECT
    name,
    category,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rank
FROM products;

3. RANK and DENSE_RANK

What it is: RANK() assigns a ranking with gaps after ties (1, 2, 2, 4). DENSE_RANK() assigns rankings without gaps (1, 2, 2, 3). Both handle ties differently than ROW_NUMBER.

Why we use it: When ranking items — "top products by price", "students by GPA", "employees by performance". RANK and DENSE_RANK handle ties naturally, while ROW_NUMBER forces uniqueness.

When we use it: When building leaderboards, ranking items within groups, or when ties should share the same rank.

-- RANK: gaps after ties (1, 2, 2, 4)
SELECT
    name,
    price,
    RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products;
 
-- DENSE_RANK: no gaps (1, 2, 2, 3)
SELECT
    name,
    price,
    DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank

4. LAG and LEAD — Access Other Rows

What it is: LAG(column, n) accesses the value from n rows before the current row. LEAD(column, n) accesses the value from n rows after. They let you compare each row to its neighbors without a self-join.

Why we use it: When calculating differences between consecutive rows — "price change from previous product", "revenue growth month-over-month", "time between user actions". LAG/LEAD do this without complex self-joins.

When we use it: When calculating trends, differences, or growth rates between consecutive rows, when building time-series comparisons, or when accessing previous/next values.

-- Previous and next product's price
SELECT
    name,
    price,
    LAG(price) OVER (ORDER BY price) AS prev_price,
    LEAD(price) OVER (ORDER BY price) AS next_price
FROM products;
 
-- Price difference from previous
SELECT
    name,
    price,
    price - LAG

5. SUM OVER — Running Totals

What it is: SUM() OVER with ORDER BY creates a running (cumulative) total. Each row's value is the sum of all previous rows up to the current one. Without ORDER BY, it returns the total for the entire partition.

Why we use it: Running totals are essential in finance (cumulative revenue), inventory (running stock levels), and analytics (progress toward goals).

When we use it: When calculating cumulative metrics, tracking progress over time, or when each row needs to know the total of all preceding rows.

-- Running total of prices
SELECT
    name,
    price,
    SUM(price) OVER (ORDER BY created_at) AS running_total
FROM products;
 
-- Total per category (without collapsing rows)
SELECT
    name,
    category,
    price,
    SUM(price) OVER (PARTITION BY category) AS category_total
FROM

6. PARTITION BY — Windows Within Groups

What it is: PARTITION BY divides the result set into groups (partitions) before applying the window function. Each partition is processed independently — rankings restart at 1, running totals reset, etc.

Why we use it: Without PARTITION BY, the window function applies to the entire result set. PARTITION BY lets you rank within categories, calculate per-group averages, or reset running totals per user.

When we use it: Every time you need a window function to operate per group — "rank products within each category", "running total per user", "average price per department".

-- Rank products within each category
SELECT
    name,
    category,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS category_rank
FROM products;
 
-- Average price per category (without losing rows)
SELECT
    name,
    category,
    price,
    AVG(price) OVER (

7. Frame Clauses

What it is: Frame clauses define exactly which rows are included in the window for each row's calculation. By default, the frame includes all rows from the partition start to the current row. You can customize it for moving averages, sliding windows, etc.

Why we use it: Default frames are often sufficient, but custom frames enable moving averages (last 3 rows), cumulative sums (all rows up to current), or future comparisons (all rows after current).

When we use it: When calculating moving averages, when you need precise control over which rows are included, or when the default frame doesn't match your business logic.

-- Moving average of last 3 products
SELECT
    name,
    price,
    AVG(price) OVER (
        ORDER BY created_at
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM products;

Common Mistakes to Avoid

1. Confusing window functions with GROUP BY — Window functions don't collapse rows
2. Forgetting ORDER BY in the window — Required for ranking and LAG/LEAD
3. Using window functions in WHERE — Use a subquery or CTE instead

Practice Exercise

1. Rank courses by price within each instructor using ROW_NUMBER
2. Find the top 2 most expensive courses per instructor
3. Use LAG to show each course's price and the previous course's price
4. Calculate a running total of course prices
5. Find courses priced above their instructor's average using a window function