Window Functions Introduction

Perform calculations across related rows without collapsing them using window functions.

Overview

Window functions compute a result across a set of rows related to the current row, without collapsing rows into a group. They are called "window" functions because they operate over a "window" of rows defined by the OVER() clause. They allow you to do things like running totals, rankings, and moving averages while keeping all rows in the result. Unlike GROUP BY aggregates, window functions return a value for every row. You can mix window functions with non-aggregated columns in the same SELECT.

Key Points

•func() OVER (PARTITION BY col ORDER BY col) basic window syntax
•PARTITION BY divides rows into groups; ORDER BY defines row order within partition
•ROW_NUMBER(): unique sequential number per row
•RANK(): rank with gaps for ties; DENSE_RANK(): rank without gaps
•SUM() OVER (): running total (no GROUP BY collapse)
•Window functions run after WHERE and GROUP BY, before ORDER BY

Examples

ROW_NUMBER, RANK, and running SUM

SELECT
  name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
  SUM(salary)  OVER (PARTITION BY department ORDER BY salary)      AS running_total
FROM employees;

Window Functions Introduction

Overview

Key Points

Examples

Exercises

Knowledge Check

How do window functions differ from GROUP BY aggregates?