What are window functions and how are they advantageous over aggregate functions?

Window functions in SQL allow performing calculations across a set of table rows that are related to the current row, without collapsing the result set. Unlike aggregate functions, which combine multiple rows into a single output (e.g., SUM, AVG with GROUP BY), window functions preserve the original number of rows, adding calculated values as additional columns.

Advantages of window functions:

• They enable calculations over a "window" of rows, such as moving sums, rankings, cumulative totals.
• They can be used alongside regular columns without grouping.
• They are useful for analytical queries where comparisons within groups or in order are needed.

Example:

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

Here, the average salary per department is calculated for each row, while each row remains in the result.