SQL Window Functions Explained with Examples

Window functions let you run calculations across a set of related rows — rankings, running totals, comparisons to the previous or next row — without collapsing your result set the way GROUP BY does. They are one of the most useful and most misunderstood parts of SQL. This guide breaks down ROW_NUMBER, RANK, DENSE_RANK, LAG, and LEAD with practical, runnable examples.

What is a Window Function?

A window function performs a calculation "over" a window of rows related to the current row, using the OVER() clause. Unlike GROUP BY, which merges rows into a single output row per group, a window function keeps every input row and simply attaches a calculated value to it.

SELECT
  employee_name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

Every employee row is preserved, but each row also shows the average salary of its department — something a plain GROUP BY query cannot do in a single pass.

ROW_NUMBER() — Unique Sequential Numbering

ROW_NUMBER() assigns a unique, sequential integer to each row within a partition, based on the ORDER BY clause. Even if two rows tie on the ordering column, they still get different numbers.

SELECT
  customer_id,
  order_date,
  order_total,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders;

-- Get each customer's most recent order:
SELECT * FROM (
  SELECT
    customer_id,
    order_date,
    order_total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

This "latest row per group" pattern is one of the most common real-world uses of ROW_NUMBER — it replaces slower correlated subqueries.

RANK() vs DENSE_RANK() — Handling Ties

RANK and DENSE_RANK both assign a rank based on ORDER BY, but they treat tied values differently:

• RANK() — leaves a gap after ties: 1, 2, 2, 4, 5
• DENSE_RANK() — no gap after ties: 1, 2, 2, 3, 4
• ROW_NUMBER() — ignores ties completely: 1, 2, 3, 4, 5

SELECT
  student_name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM exam_results;

-- score 95, 95, 90, 85
-- RANK:       1, 1, 3, 4
-- DENSE_RANK: 1, 1, 2, 3

Use DENSE_RANK for "top N distinct score bands" and RANK when you want the gap to reflect exactly how many rows tied for a position.

LAG() and LEAD() — Comparing Rows

LAG() looks at a previous row's value, and LEAD() looks at a following row's value, within the same partition and order. They are ideal for month-over-month comparisons, detecting changes, and calculating differences between consecutive rows.

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly_revenue
ORDER BY month;

Both functions accept an optional offset and default value: LAG(revenue, 1, 0) looks one row back and returns 0 instead of NULL if there is no previous row.

Running Totals and Moving Averages

Aggregate functions like SUM and AVG also work as window functions. Combined with a frame clause, they can compute running totals or moving averages:

SELECT
  order_date,
  daily_sales,
  SUM(daily_sales) OVER (ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  AVG(daily_sales) OVER (ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7day
FROM daily_sales_summary
ORDER BY order_date;

The ROWS BETWEEN ... AND CURRENT ROW frame clause controls exactly which rows are included in each calculation — this works the same in MySQL 8+, PostgreSQL, and SQL Server.

Window Functions vs GROUP BY — Key Difference

• GROUP BY — collapses rows into one row per group, loses row-level detail
• Window functions — keeps every row, attaches group-level or ranked context to each
• You can use both together: aggregate with GROUP BY first, then rank the aggregated results with a window function in an outer query

Frequently Asked Questions