What are window functions? Which ones have you used? How do they differ from GROUP BY?

Window functions in SQL allow performing calculations across a set of table rows that are related to the current row, without grouping the data into a single row as GROUP BY does. They operate over a set of rows while preserving the original number of rows in the result.

Examples of window functions include ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER(), etc.

Differences from GROUP BY:

• GROUP BY aggregates rows, reducing multiple rows into one, while window functions return a result for each row, considering the window context.
• Window functions use the OVER() clause, where you can define the window (partition by, order by) over which the calculation occurs.

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, but rows are not aggregated.

Functions used: ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER().