What are window functions in SQL?

Window functions perform calculations across a set of related rows without collapsing them into groups - like aggregate functions but each row keeps its identity.

SQLPostgreSQL

Overview

Window functions (also called analytic functions) calculate values across a "window" of rows related to the current row. Unlike `GROUP BY`, the rows are not collapsed - you get one output row per input row. They are used for rankings, running totals, moving averages, and comparing rows to their neighbours.

Key Points

•Syntax: `function() OVER (PARTITION BY ... ORDER BY ...)`.
•`PARTITION BY`: splits rows into groups (like GROUP BY but without collapsing).
•`ORDER BY` inside OVER: defines row order for running totals, rankings, etc.
•Common functions: `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `LAG()`, `LEAD()`, `SUM() OVER`, `AVG() OVER`.
•`LAG(col, n)` accesses the value n rows before the current row; `LEAD` goes forward.
•Window functions run after WHERE/GROUP BY/HAVING but before ORDER BY/LIMIT.

Examples

ROW_NUMBER: assign a rank within each department

sql

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

-- Each department gets its own rank sequence: 1, 2, 3...
-- The rows are not collapsed - every employee appears

SUM OVER: running total of sales

sql

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

-- sale_date | amount | running_total
-- 2025-01-01|    100 |           100
-- 2025-01-02|    250 |           350
-- 2025-01-03|     80 |           430

LAG and LEAD: compare to previous/next row

sql

SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month_change
FROM monthly_revenue;

RANK vs DENSE_RANK vs ROW_NUMBER on ties

sql

SELECT score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores;

-- score | row_num | rank | dense_rank
--   100 |       1 |    1 |          1
--    90 |       2 |    2 |          2
--    90 |       3 |    2 |          2   ← tie
--    80 |       4 |    4 |          3   ← RANK skips 3; DENSE_RANK does not

Pro Tip

Window functions unlock analysis that would require self-joins or subqueries otherwise. If you need "the top N per group", "running totals", or "compare each row to the previous one", reach for a window function - it will be cleaner and more performant.