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.

[!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. Window Functions: Seeing Through the Data

2. 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”).

3. Window Functions: Seeing Through the Data

4. 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;

5. Window Functions: Seeing Through the Data

6. 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;

7. Window Functions: Seeing Through the Data

8. 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
)

9. Window Functions: Seeing Through the Data

10. 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

SQL Explanation: SELECT func() OVER (...)

Result Set

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

11. Window Functions: Seeing Through the Data

[!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.

12. 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.