SQL Window Functions — ROW_NUMBER, RANK, LAG, LEAD

Window Functions Explained

Window functions perform calculations across a set of rows related to the current row without collapsing them into a single output row (unlike GROUP BY).

Syntax

function_name() OVER (
  [PARTITION BY col1, col2]
  [ORDER BY col3 ASC|DESC]
  [frame_clause]
)

Ranking Functions

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

Function	Ties	Gaps
ROW_NUMBER	Each row gets a unique number	No
RANK	Tied rows get the same number	Yes (next rank skips)
DENSE_RANK	Tied rows get the same number	No

Top-N Per Group

SELECT * FROM (
  SELECT name, department_id, salary,
    ROW_NUMBER() OVER (
      PARTITION BY department_id ORDER BY salary DESC
    ) AS rn
  FROM employees
) sub WHERE rn <= 3;

LAG and LEAD

SELECT month, revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change
FROM monthly_sales;

Running Totals and Moving Averages

SELECT month, revenue,
  SUM(revenue) OVER (ORDER BY month) AS running_total,
  AVG(revenue) OVER (
    ORDER BY month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3m
FROM monthly_sales;

Availability

• PostgreSQL: Full support since 8.4
• MySQL: Full support since 8.0 (not available in 5.7)
• SQLite: Full support since 3.25.0