There is a moment in most analysts’ careers where they stop writing SQL that works and start writing SQL that is good. For a lot of people, that moment is window functions.
Before you learn them, certain problems feel genuinely impossible in SQL. Rank each salesperson within their region. Show each month alongside the previous month. Calculate a running total. Find the most recent record per customer. You end up exporting to Excel, or writing a self-join that makes your eyes water, or looping in Python over something the database could have answered in one pass.
After you learn them, those problems take four lines.
This article is the explanation I wish someone had given me, without the dense syntax reference that most tutorials open with.
The core idea, in plain language
Start with what you already know. GROUP BY collapses rows. If you have 500 sales rows across 5 regions and you group by region, you get 5 rows back. The individual rows are gone.
That is usually what you want. Sometimes it is not.
Suppose you want to see every sale, and next to each one, the average sale for that region. GROUP BY cannot do this, because it has already thrown away the individual sales. You have to compute the average separately and join it back.
A window function computes across a set of related rows, but does not collapse them. Every input row still comes out. You just get an extra column with something calculated from that row’s neighbours.
That is the whole idea. Everything else is syntax.
The syntax, stripped down
sql
AVG(sale_amount) OVER (PARTITION BY region)Read it in three parts:
AVG(sale_amount)is the calculation. Normal aggregate function.OVERis what makes it a window function. WithoutOVER, this needs aGROUP BY. WithOVER, it does not.PARTITION BY regiondefines the window: which other rows should this row look at. Here, “everyone in my region”.
If you leave out PARTITION BY entirely, the window is the whole result set. AVG(sale_amount) OVER () gives every row the overall average.
PARTITION BY is doing the same job as GROUP BY, but without the collapsing. That one sentence unlocks most of the confusion people have.
Ranking, which is where most people start
The classic interview question: for each department, find the top 3 highest-paid employees.
Try it without window functions and you will end up somewhere unpleasant. With them:
sql
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employeesRead the OVER clause as a sentence: for each department, order the rows by salary descending, and number them.
Then wrap it to filter, because you cannot filter on a window function in the same WHERE clause. The window is computed after WHERE runs, so the column does not exist yet. Use a CTE:
sql
WITH ranked AS (
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees
)
SELECT employee_name, department, salary
FROM ranked
WHERE rank_in_dept <= 3
ORDER BY department, rank_in_deptThat CTE wrapper trips up almost everyone the first time. It is not a limitation you can argue with, it is just how the order of execution works.
ROW_NUMBER vs RANK vs DENSE_RANK
Three ranking functions, and the difference only matters when there are ties. Say three people earn 90,000 and one earns 85,000:
ROW_NUMBER()gives 1, 2, 3, 4. Always sequential, ties broken arbitrarily.RANK()gives 1, 1, 1, 4. Ties share a rank, then it skips.DENSE_RANK()gives 1, 1, 1, 2. Ties share a rank, no gap.
Which to use depends on the question. “Give me exactly one row per department” wants ROW_NUMBER(). “Who is in the top 3 salary bands” wants DENSE_RANK(). “Standard competition ranking, like a race” wants RANK().
One warning from experience: if you use ROW_NUMBER() to deduplicate and your ORDER BY has ties, the row you keep is not deterministic. Add a tiebreaker column, usually a primary key, so the same query returns the same result twice.
LAG and LEAD, the month-over-month workhorses
This pair probably saves me more time than anything else in SQL.
LAG() gets a value from a previous row. LEAD() gets one from a following row. “Previous” means previous in the order you specify.
Month-over-month growth, the most requested calculation in any reporting job:
sql
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue
ORDER BY monthThe first row gets NULL for the previous month, which is correct. There is no previous month. If you want a default instead, LAG(revenue, 1, 0) supplies one, where the second argument is how many rows back and the third is the fallback.
Add PARTITION BY and you get it per product, per region, per whatever, in the same query:
sql
LAG(revenue) OVER (PARTITION BY product_line ORDER BY month)A practical use I reach for often: measuring the gap between consecutive events for the same user, like time between logins or between a patient’s visits. LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) and then a date difference. Three lines for something that is genuinely awkward otherwise.
Running totals and the frame clause
Running totals are where window functions stop being obvious, because of a part of the syntax most tutorials mention too late.
sql
SELECT
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM salesWhy does adding ORDER BY turn a total into a running total? Because of a default you did not write. When you include ORDER BY in a window without specifying a frame, SQL assumes you mean “from the start of the partition up to the current row”. That cumulative default is what makes it run.
Without ORDER BY, the frame is the entire partition, so you get the same grand total on every row.
You can state the frame explicitly:
sql
SUM(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7_dayThat is a 7-day rolling sum, assuming one row per day. The frame clause is the difference between a running total and a moving average, and it is worth ten minutes of your attention because it is the part that will bite you.
A subtlety worth knowing: ROWS counts physical rows, RANGE counts by value. If your data has gaps (missing days) or duplicates (two rows for the same date), these give different answers. ROWS is what people usually mean.
Where this shows up in real work
A few patterns I use constantly:
Latest record per group. Rank by timestamp descending within the entity, keep rank 1. Far cleaner than a correlated subquery, and usually faster.
Percentage of total. amount / SUM(amount) OVER (PARTITION BY region) * 100 gives each row’s share of its region, without a join.
Deduplication. Rank by a business key, keep the first, drop the rest. Add that tiebreaker.
Sessionisation. Use LAG to find the gap since the previous event, flag gaps over 30 minutes, then a cumulative SUM of those flags becomes a session ID. This one feels like a magic trick the first time it works.
Cohort retention. FIRST_VALUE() over the user’s events ordered by date gives you their signup cohort on every row, ready to pivot.
How to actually learn them
Reading about window functions does not work. I have watched people read three articles and still freeze in an interview.
What works is writing them against data you can see. Pick a small table, twenty rows, and write the same query with ROW_NUMBER, RANK and DENSE_RANK side by side so you can watch the numbers differ. Then add PARTITION BY and watch the numbering restart. Then remove ORDER BY from a SUM and see the running total flatten into a constant.
Fifteen minutes of that does more than an afternoon of reading, because the mental model you need is visual: which rows can this row see.
Once it clicks, it stays clicked. And you will find yourself deleting a lot of old Python that was doing work the database should have been doing all along.