Aggregates with GROUP BY squash many rows into one. Window functions calculate across rows but keep every row. You add OVER (...) to say which rows to look at and in what order:
SELECT first_name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS pay_rank
FROM employees;
PARTITION BY splits rows into groups (here, departments) and restarts the numbering in each; leave it out to rank across the whole table. The ORDER BY inside OVER decides who comes first.
The three ranking functions differ only in how they treat ties:
ROW_NUMBER() gives 1, 2, 3, 4 even when values tie. Which tied row gets the lower number is arbitrary.RANK() gives tied rows the same number, then skips: 1, 2, 2, 4.DENSE_RANK() gives tied rows the same number with no gap: 1, 2, 2, 3.
Window functions run after WHERE and GROUP BY, so you can't filter on them in WHERE. The next lesson shows how to get the top rows in each group.