What is the purpose of window functions in SQL and how do they differ from regular aggregate functions?

Window functions in SQL allow performing aggregate or analytical calculations over a set of rows related to the current row, without collapsing the result into a single row as regular aggregate functions do.

Main differences between window functions and aggregate functions:

• Window functions are calculated over a "window" of rows defined by the OVER() clause, and return a value for each row in the original dataset.
• Aggregate functions (e.g., SUM(), COUNT()) typically group rows and return a single value per group, reducing the number of rows in the result.

Example of using a window function:

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

Here, the average salary per department is calculated for each row without grouping the rows, allowing you to see both individual salaries and the department average simultaneously.

Thus, window functions extend data analysis capabilities while preserving row-level detail.