SQL Window Functions for Beginners

1What Are SQL Window Functions?

A SQL window function performs a calculation across a set of rows related to the current row — the window — while still returning every individual row. This is the key difference from GROUP BY: a window function lets you show each row and a summary value at the same time, such as each sale next to its running total.

Window functions answer questions that are awkward or impossible with plain aggregates: rank customers by spend within each region, compare a month to the previous month, or number rows to remove duplicates. Once they click, they replace piles of self-joins and subqueries with a single readable clause.

2The OVER Clause

Every window function uses an OVER clause that defines which rows make up the window. Inside OVER, PARTITION BY splits the data into groups, and ORDER BY sequences the rows within each group. Both are optional, but together they control exactly how the function sees the data.

Think of PARTITION BY as resetting the calculation for each group, and ORDER BY as deciding the running order within a group. Leaving out PARTITION BY treats the whole result set as one window.

• SUM(amount) OVER () # total across all rows
• SUM(amount) OVER (PARTITION BY region) # total per region, repeated on each row
• SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) # running total per region
• The window resets at each new partition value.

3Ranking Functions

Ranking functions assign a position to each row within its window. ROW_NUMBER gives a unique sequential number even for ties, RANK leaves gaps after ties, and DENSE_RANK ranks ties equally without gaps. Choosing among them depends on how you want to treat equal values.

A classic use is finding the top N per group, such as the three highest-paid employees in each department. You number the rows with ROW_NUMBER partitioned by department and ordered by salary, then filter to the first three in an outer query.

• ROW_NUMBER() OVER (ORDER BY salary DESC) # 1,2,3,4 — always unique
• RANK() OVER (ORDER BY salary DESC) # 1,2,2,4 — gaps after ties
• DENSE_RANK() OVER (ORDER BY salary DESC) # 1,2,2,3 — no gaps
• NTILE(4) OVER (ORDER BY salary) # splits rows into 4 buckets

4LAG and LEAD: Looking at Neighbors

LAG and LEAD let a row read a value from a previous or following row within the window. LAG looks backward and LEAD looks forward, both by a configurable number of rows. This makes period-over-period comparisons simple.

For example, to compute month-over-month growth, order the rows by month and use LAG to pull the previous month's revenue onto the current row, then subtract. Without window functions this would require an awkward self-join on a shifted date.

• LAG(revenue) OVER (ORDER BY month) # previous month's revenue
• LEAD(revenue) OVER (ORDER BY month) # next month's revenue
• revenue - LAG(revenue) OVER (ORDER BY month) # month-over-month change
• LAG(revenue, 1, 0) # default to 0 when there is no previous row

5Running Totals and Moving Averages

Standard aggregate functions like SUM, AVG, COUNT, MIN, and MAX become window functions when paired with OVER and an ORDER BY. Adding ORDER BY inside OVER turns a plain aggregate into a running one that accumulates row by row.

You can further narrow the window with a frame clause such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, which restricts the calculation to a sliding range. That frame is exactly how you build a moving average over the last three rows.

AVG(sales) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)  # 3-day moving average
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  # running total (default)

6Window Functions vs GROUP BY

GROUP BY collapses many rows into one summary row per group, so you lose the detail. A window function keeps every row and attaches the summary to each one. If you need both the individual records and a group calculation in the same result, a window function is the tool.

They also run at different times. Window functions execute after WHERE, GROUP BY, and HAVING, which means you cannot filter on a window function's result in the same query's WHERE clause. Wrap the query in a subquery or common table expression and filter there instead.

7Common Mistakes to Avoid

Most beginner errors come from misunderstanding when window functions run or how the window is defined.

• Trying to filter a window result in WHERE — move it to a CTE or subquery.
• Forgetting ORDER BY inside OVER when you expect a running total, which gives a full-partition total instead.
• Confusing RANK and DENSE_RANK, producing unexpected gaps in ranking.
• Omitting PARTITION BY when the calculation should reset per group.
• Assuming LAG on the first row returns something other than NULL — supply a default if you need one.

8Key Takeaways

Window functions unlock analytics that plain aggregates cannot express.

• They calculate across related rows without collapsing them like GROUP BY does.
• The OVER clause with PARTITION BY and ORDER BY defines the window.
• ROW_NUMBER, RANK, and DENSE_RANK rank rows; LAG and LEAD read neighbors.
• SUM or AVG with ORDER BY produces running totals and moving averages.
• Filter window results in a subquery or CTE, never the same WHERE clause.

9Frequently Asked Questions

Q: What is the difference between a window function and GROUP BY? A: GROUP BY collapses rows into one summary per group, removing the detail, while a window function keeps every row and adds the calculation alongside it. Use a window function when you need both the individual rows and a group-level value in the same output.

Q: What is the difference between ROW_NUMBER, RANK, and DENSE_RANK? A: ROW_NUMBER always assigns unique consecutive numbers even to ties, RANK gives ties the same number but leaves gaps afterward, and DENSE_RANK gives ties the same number with no gaps. Pick based on how you want tied values handled.

Q: Why can't I filter on a window function in WHERE? A: Window functions execute after WHERE, GROUP BY, and HAVING, so their results do not exist yet when WHERE runs. Wrap the query in a common table expression or subquery and apply the filter in the outer query.

Q: Do all databases support window functions? A: All modern relational databases support them, including PostgreSQL, SQL Server, Oracle, and MySQL 8.0 and later. Very old MySQL versions before 8.0 lacked them, so upgrade if you are on an older release.