Window Functions

Window functions perform calculations across a set of rows related to the current row — without collapsing them into a single output row like GROUP BY. This lesson covers ranking, offset, aggregate window functions, PARTITION BY, and frame clauses.

---

What Are Window Functions?

A window function operates on a "window" (a set of rows) defined relative to the current row:

SELECT
  employee_id,
  department,
  salary,
  AVG(salary) OVER () AS company_avg,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Unlike GROUP BY, every original row is preserved in the output.

---

Ranking Functions

ROW_NUMBER

Assigns a unique sequential number within each partition:

SELECT
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  employee_id,
  department,
  salary
FROM employees;

RANK and DENSE_RANK

SELECT
  name,
  score,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM exam_results;

Score	RANK	DENSE_RANK
95	1	1
95	1	1
88	3	2
80	4	3