SQL Window Functions Explained

The core idea

A window function calculates across related rows while keeping each original row visible. GROUP BY collapses rows; a window function annotates them.

SELECT
  employee_id,
  department_id,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) AS department_avg
FROM employees;

Ranking functions

Use ROW_NUMBER when every row needs a unique sequence, RANK when ties should leave gaps, and DENSE_RANK when ties should not leave gaps.

SELECT
  product_id,
  category_id,
  revenue,
  DENSE_RANK() OVER (
    PARTITION BY category_id
    ORDER BY revenue DESC
  ) AS category_rank
FROM product_sales;

Previous rows and running totals

LAG compares the current row with a previous row. SUM with an ordered window creates a running total.

• Always define a deterministic ORDER BY
• Partition only when the calculation must restart
• Check ties and missing dates
• Name calculated columns clearly