What are window functions in SQL

· Category: SQL & Databases

Short answer

Window functions compute values across a set of rows related to the current row, defined by an OVER clause, without collapsing the result set like GROUP BY.

Steps

  1. Rank rows: SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;
  2. Partition by group: SELECT name, department, salary, AVG(salary) OVER (PARTITION BY department) FROM employees;
  3. Running total: SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM transactions;
  4. Frame specifications: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  5. Common functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), NTILE()

Tips

  • Window functions can appear in SELECT and ORDER BY but not in WHERE.
  • Combine multiple window definitions with named windows using the WINDOW clause.

Common issues

  • Forgetting OVER() turns a window function into an ordinary aggregate, causing syntax errors.
  • RANK() leaves gaps after ties; use DENSE_RANK() for continuous ranking.r