What are window functions in SQL and how do they differ from GROUP BY?

Window functions in SQL allow you to perform calculations across a set of table rows that are related to the current row, without collapsing the result set. They operate on a "window" — a set of rows defined by the OVER() clause — and can be used for calculating aggregates, ranking, moving averages, and other analytical tasks without grouping the data.

Unlike GROUP BY, which aggregates rows and returns a single row per group, window functions return a result for each row in the original set, considering the window context.

Example:

SELECT
  employee_id,
  department_id,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary
FROM employees;

Here, each row shows the employee's salary and the average salary for their department, with the total number of rows remaining unchanged.

GROUP BY, on the other hand, groups rows by department_id and returns one row per department with an aggregated value.