Window Functions

Overview

Window functions perform calculations across a set of rows related to the current row without collapsing them into a single output row like GROUP BY does. They are defined with an OVER clause that can PARTITION BY groups and ORDER BY rows. Common uses include ranking, running totals, and comparing a row to its neighbors.

Syntax / Usage

The OVER clause defines the window. PARTITION BY splits rows into groups; ORDER BY orders rows within each partition; an optional frame clause limits which rows contribute.

SELECT
  employee,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  SUM(salary) OVER (
    ORDER BY salary
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM employees;

Examples

Rank products by sales within each category and keep only the top one:

WITH ranked AS (
  SELECT category, product, sales,
         RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS r
  FROM product_sales
)
SELECT category, product, sales
FROM ranked
WHERE r = 1;

Compare each month's revenue to the previous month with LAG:

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

Common Mistakes

• Using a window function in a WHERE clause (wrap it in a CTE or subquery first)
• Confusing RANK (leaves gaps after ties) with DENSE_RANK (no gaps) and ROW_NUMBER (always unique)
• Omitting ORDER BY for ranking or running-total functions that require it
• Forgetting PARTITION BY, causing the window to span the entire result set
• Assuming a default frame; running aggregates need an explicit or ordered frame

See Also

group-by sql-cte select