What are window functions used for in SQL?

Window functions in SQL perform aggregation of data within a set of rows related to the current row, but do not group the resulting data set. This allows for calculations such as cumulative sums, moving averages, or ranking, while preserving all rows of the original query.

Main purposes of using window functions:

• Ranking: Assigning a rank to each row within a specific window.

  -- Get employee rank by salary within each department
  SELECT
      employee_name,
      salary,
      department,
      RANK() OVER (PARTITION BY department ORDER BY salary DESC) as department_rank
  FROM
      employees;
  

• Calculating statistics over a group of rows without grouping: Aggregating data within a "window" of rows, maintaining row-level detail.

  -- Calculate average salary across the entire department for each row
  SELECT
      employee_name,
      salary,
      department,
      AVG(salary) OVER (PARTITION BY department) as avg_department_salary
  FROM
      employees;
  

• Comparing the current row with other rows in the window: Accessing data from previous or subsequent rows within the window.

  -- Get the salary of the previous employee in the department by descending salary
  SELECT
      employee_name,
      salary,
      department,
      LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary DESC) as previous_salary
  FROM
      employees;
  

• Calculations with cumulative totals: Computing sums, averages, and other aggregations cumulatively.

  -- Calculate cumulative sales sum by date
  SELECT
      sale_date,
      amount,
      SUM(amount) OVER (ORDER BY sale_date) as cumulative_sales
  FROM
      sales;
  

Unlike regular aggregate functions (GROUP BY), window functions do not collapse rows, making them a powerful tool for analytical queries and detailed data analysis.