SQL Window Functions: A Complete Guide

ROW_NUMBER()

Assigns a unique sequential integer to each row within a partition.

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

RANK vs DENSE_RANK

RANK() leaves gaps: 1, 2, 2, 4
DENSE_RANK() no gaps: 1, 2, 2, 3

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

What are LAG and LEAD?

LAG accesses a previous row's value. LEAD accesses a next row's value. Essential for period-over-period comparisons.

Revenue change example

WITH monthly AS (
  SELECT SUBSTR(order_date,1,7) AS month,
         ROUND(SUM(total),2) AS revenue
  FROM orders GROUP BY month
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change
FROM monthly ORDER BY month;