You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
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.
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.
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;
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 |
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.