Window FunctionsIntermediate
1Question 1 of 2
sqlWhat are SQL Window Functions and how does the OVER (PARTITION BY ... ORDER BY ...) clause work?
Explanation & Concept
Window functions perform calculations across a set of table rows related to the current row without collapsing them into a single row like `GROUP BY` does. Each row in the original query retains its identity while having access to aggregated or ranked information across its 'window'.
`PARTITION BY` defines how rows are grouped into windows, and `ORDER BY` defines the sequence of rows within each partition for ranking or running totals.
Code Example
sql
SELECT
employee_id,
department_id,
salary,
-- Rank employee within their department by salary
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS dept_salary_rank,
-- Running total of salary within department
SUM(salary) OVER (
PARTITION BY department_id
ORDER BY hire_date
) AS running_dept_payroll
FROM employees;How It Works & Key Takeaways
Unlike a `GROUP BY` query which reduces all department rows to one, window functions allow you to print every employee alongside their relative department salary rank and cumulative payroll up to their hire date.