What are SQL Window Functions and how do they work?

SQL Window Functions let you perform calculations across a set of rows that are related to the current row, without grouping or removing rows. They use the OVER() clause with PARTITION BY (to group rows) and ORDER BY (to define order).

Example – ranking employees by salary in each department:

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

Common uses: ranking, running totals, moving averages, and comparing current rows with previous/next ones.