Introduction to SQL Window Functions

What are Window Functions?

Window functions perform calculations across a set of rows related to the current row, without collapsing them into a single output row. Unlike aggregate functions with GROUP BY, window functions preserve individual rows in the result.

Syntax

SELECT column,
  window_function() OVER (
    PARTITION BY partition_column
    ORDER BY order_column
  )
FROM table_name;

When to Use

• Ranking rows within groups
• Calculating running totals
• Comparing each row to a group average
• Accessing previous or next row values

Key Points

1. OVER Clause — Every window function requires an OVER clause. An empty OVER() treats all rows as one window.
2. PARTITION BY — Divides rows into groups (partitions). The function resets for each partition.
3. ORDER BY in OVER — Defines the order of rows within each partition.
4. No Row Reduction — Unlike GROUP BY, window functions keep every row in the output.
5. Frame Clause — Optionally define a frame: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
6. Common Functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM, AVG, NTILE.