What are window functions in PostgreSQL and how do they work

Window Functions

Window functions compute a value for each row based on a related set of rows — without collapsing them like GROUP BY does.

Syntax

function_name() OVER (
  PARTITION BY column
  ORDER BY column
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Ranking

SELECT
  name,
  salary,
  department_id,
  ROW_NUMBER()  OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn,
  RANK()        OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
  DENSE_RANK()  OVER (PARTITION BY department_id ORDER BY salary DESC) AS drnk
FROM employees;

RANK skips numbers after ties; DENSE_RANK does not.

LAG and LEAD

SELECT date, revenue,
  LAG(revenue) OVER (ORDER BY date)  AS prev_day,
  LEAD(revenue) OVER (ORDER BY date) AS next_day
FROM daily_sales;

Running total

SELECT date, amount,
  SUM(amount) OVER (ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM transactions;

Use a CTE or subquery to filter on a window function result — you cannot use WHERE on a window function in the same query level.