Window Functions

20 min

Explanation

A GROUP BY query collapses many rows into one row per group — you lose the individual rows. A window function computes something across a group of related rows too, but keeps every original row intact, attaching the computed value as an extra column.

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

PARTITION BY department says "compute this independently within each department" (like GROUP BY, but without collapsing rows). ORDER BY salary DESC says "rank within that partition, highest salary first."

Try it

ROW_NUMBER() OVER (ORDER BY ...) with no PARTITION BY ranks across the whole table — that's the 'who's #1 overall' pattern. RANK() differs from ROW_NUMBER() only when there are ties: RANK() gives tied rows the same rank and skips the next number; ROW_NUMBER() never ties.

Loading editor…

Explanation

Aggregate functions (SUM, AVG, COUNT...) work as window functions too — combined with ORDER BY inside OVER(...), SUM becomes a running total instead of one final number, since each row only sees the rows up to and including itself in that ordering:

SELECT name, salary,
       SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees;

Add PARTITION BY and the running total resets per group — "running total of salary within each department, in employee-id order" is exactly what the next exercise asks for.

Exercise

The `employees` table has columns `id, name, department, salary`. Write a query using `RANK()` to rank employees by salary WITHIN their department (highest salary = rank 1). Return `name`, `department`, `salary`, and the rank as `dept_rank`, ordered by department then dept_rank.

Exercise

Write a query that returns `name`, `department`, `salary`, and a running total of salaries WITHIN each department (ordered by employee id) as `running_total`. Order the final result by department, then id.

Quiz

What does PARTITION BY do inside a window function's OVER(...)?

Checkpoint

You can rank rows within groups using RANK()/ROW_NUMBER() and compute running totals with SUM() OVER(...), both without collapsing the original rows.