SQL Window Functions

HR wants every employee's salary shown next to their department's average, so each person can see where they stand, without losing any individual rows the way group-by-department would. That's precisely what a window function does and GROUP BY can't.

Learning Objectives

• Define a window function precisely, and state exactly how it differs from GROUP BY.
• Write a window function using OVER and PARTITION BY.
• Know the minimum version each database in this course needs for window function support.

What a Window Function Actually Is

A window function performs a calculation across a set of rows related to the current row, its "window", without collapsing those rows into fewer output rows. This is the one thing that actually distinguishes it from GROUP BY: GROUP BY department_id turns many employee rows into one row per department, a window function keeps every single employee row, and just attaches a calculated value (like that department's average) alongside each one.

OVER and PARTITION BY

OVER () is what turns AVG() into a window function instead of a regular aggregate, PARTITION BY department_id tells it to compute each employee's dept_avg_salary using only that employee's own department's rows, exactly like a GROUP BY department_id would group for an aggregate, but without collapsing anything. Leave PARTITION BY out entirely and the window covers every row in the whole table.

SELECT first_name, last_name, department_id, salary,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM employees;

Minimum Version for Window Function Support

Try It Yourself

Write a query showing every employee's first_name, salary, and the overall company-wide average salary alongside each row, on PostgreSQL.

SELECT first_name, salary,
       AVG(salary) OVER () AS company_avg_salary
FROM employees;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Reach for a window function the moment a query needs a group-level value alongside individual row detail, that combination is exactly what GROUP BY can't do alone.
• Check the target database's actual version against the minimum-version table before relying on window functions in a long-lived production system.
• Name window function result columns descriptively (dept_avg_salary, not avg1), they sit alongside plain columns and should read clearly.

Interview Questions

Summary

A window function calculates across a related set of rows without collapsing them, the one real distinction from GROUP BY. OVER() marks a function as a window function, PARTITION BY defines which rows belong together for that calculation, and every database in this course has supported the feature for years in its current form.

What's Next?

SQL ROW_NUMBER() is next: the simplest window function, giving every row within a partition a unique, sequential number.