advancedAdvanced SQL100% Free

SQL Window Functions

Definition

SQL window functions perform calculations across a set of table rows related to the current row without collapsing rows into groups, unlike GROUP BY.

Introduction to SQL Window Functions

Window functions are one of the most powerful SQL features for analytics. Unlike GROUP BY which collapses rows, window functions compute a result for each row while still having access to other rows in the 'window'. They're essential for ranking, running totals, moving averages, and comparing a row to its neighbours.

Syntax

SQL
SELECT column,
  FUNC() OVER (
    PARTITION BY partition_col
    ORDER BY sort_col
    ROWS BETWEEN ... AND ...
  ) AS result
FROM table;

Examples

1

ROW_NUMBER — Rank Within Groups

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

Assigns a unique rank to each employee within their department, starting at 1 for the highest-paid.

Try in Playground
2

Running Total with SUM OVER

SELECT order_date, amount,
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders;

Calculates a cumulative running total of order amounts, growing with each row.

Try in Playground
3

LEAD and LAG — Compare Adjacent Rows

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

LAG looks back at the previous row. This calculates month-over-month revenue growth.

Try in Playground

Common Mistakes

Confusing RANK and DENSE_RANK — RANK skips numbers after ties, DENSE_RANK doesn't

Forgetting PARTITION BY — without it, the window spans the entire table

Using window functions in WHERE clause (not allowed) — wrap in a subquery or CTE

Mixing up ROWS BETWEEN and RANGE BETWEEN — they behave differently with duplicate values

Frequently Asked Questions

What are SQL window functions?
Window functions compute values for each row based on a 'window' of related rows, defined by OVER(). Unlike GROUP BY, they don't collapse rows. Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER().
What is the difference between RANK and DENSE_RANK?
Both rank rows, but they handle ties differently. If two rows tie for rank 2, RANK gives both a 2 but skips 3 (next rank is 4). DENSE_RANK gives both a 2 and continues with 3 without skipping.
What does PARTITION BY do in a window function?
PARTITION BY divides the result set into partitions and the window function is applied to each partition independently. Similar to GROUP BY but without collapsing rows. For example, PARTITION BY department makes ROW_NUMBER restart at 1 for each department.
Can window functions be used in WHERE clause?
No. Window functions are evaluated after WHERE filtering. To filter based on a window function result, wrap it in a subquery or CTE: WITH ranked AS (SELECT *, ROW_NUMBER() OVER(...) AS rn FROM t) SELECT * FROM ranked WHERE rn = 1;

Related SQL Topics

Group ByCte Common Table ExpressionsJoinsQuery OptimizationAggregate Functions

Practice This in the SQL Playground

Write real queries, see live results, and master SQL Window Functions hands-on. 100% free.

Open SQL PlaygroundBrowse All Lessons