Tell about window functions in SQL.

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. They are useful for calculating aggregates, ranking, moving averages, and other analytical tasks.

Main features:

• Operate over a window of rows defined by OVER().
• Do not reduce the number of rows in the result, unlike GROUP BY.
• Allow the use of ranking functions (ROW_NUMBER(), RANK()), aggregate functions (SUM(), AVG()), and others.

Example: get the employee's rank within the department by salary:

SELECT
  employee_id,
  department_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;

Here, PARTITION BY divides the data by departments, and ORDER BY sorts by salary within each department. The ROW_NUMBER() function assigns a row number within each group.

Thus, window functions extend the analytical capabilities in SQL, allowing working with data in the context of neighboring rows without losing detail.