Here’s a fact that trips up a lot of people learning SQL: you can calculate a SUM, a COUNT, or a running total across a group of rows without collapsing those rows into a summary — and without writing a single GROUP BY clause. For years I didn’t believe this was worth learning properly, so I worked around it with clunky subqueries and self-joins whenever I needed that kind of calculation. Every explanation I found jumped straight into PARTITION BY and OVER without ever pausing to explain what problem those keywords were solving.
The breakthrough came from a simpler mental model, not from memorizing syntax: window functions let you perform a calculation across a related group of rows while still keeping every individual row visible in your result, instead of collapsing those rows down into one summary row the way GROUP BY does.
How Window Functions Differ From GROUP BY
This distinction is the entire foundation for understanding window functions, and it builds directly on the GROUP BY mental model from an earlier tutorial on this blog.
Recall that GROUP BY sorts rows into separate piles and then shows you one summary row per pile — the individual rows disappear into that summary. Window functions perform a similar grouping and calculation, but they preserve every original row in the result, adding the calculated group-level value as an extra column next to each row rather than collapsing everything down into one row per group.
That’s the real power here: seeing both the row-level detail and a value calculated from that row’s broader group, at the same time, in the same row of output. GROUP BY alone simply can’t do this, since collapsing rows into summaries is the whole point of GROUP BY.
A Concrete Motivating Example
Picture a sales table full of individual transactions. You want to show each transaction’s amount alongside that salesperson’s total sales across all their transactions combined — both pieces of information sitting together on every single transaction row.
GROUP BY alone can’t get you there, because it would collapse each salesperson’s transactions down into one summary row showing only the total, and the transaction-level detail would be gone. A window function can calculate that same salesperson total while still showing every transaction row separately, repeating the calculated total as an extra column value next to each of that salesperson’s individual transactions.
The Basic Window Function Syntax
The general shape looks like this: an aggregate function (SUM, COUNT, AVG, or something like RANK or ROW_NUMBER, both covered below), followed by OVER, followed by a parenthesized specification of exactly how that calculation’s “window” of related rows should be defined.
For the salesperson total example: SUM of sale amount, OVER, opening parenthesis, PARTITION BY salesperson, closing parenthesis. This sums sale amount, but calculates it separately for each unique salesperson rather than producing one grand total across everyone combined.
PARTITION BY is the keyword defining your window’s boundaries — conceptually close to GROUP BY’s grouping logic, just without collapsing the individual rows away. Skip PARTITION BY entirely, and the window function treats every row in your result as one single window, much like an aggregate function with no GROUP BY produces one overall result.
Adding This to a Complete Query
Put together in a full SELECT statement: SELECT the salesperson column, the individual sale amount column, plus the window function expression (SUM of sale amount OVER PARTITION BY salesperson), then FROM your sales table.
Notice there’s no GROUP BY anywhere in this query, even though we’re calculating a SUM. That’s the key distinguishing feature of window functions: they let you use aggregate-style calculations without requiring — or even allowing — the GROUP BY clause that would normally be necessary, because the OVER clause with its PARTITION BY handles that grouping logic in a way that preserves individual rows instead of collapsing them.
ROW_NUMBER: Assigning a Sequential Number Within Each Group
Beyond standard aggregate functions repurposed as window functions, some functions exist only to be used this way, and ROW_NUMBER is one of the most useful.
ROW_NUMBER assigns a sequential number (1, 2, 3, and onward) to each row within its partition, based on whatever order you specify. The syntax: ROW_NUMBER, with empty parentheses since it takes no direct arguments, then OVER, then a parenthesized specification with both PARTITION BY (defining your groups) and ORDER BY (defining the sequence within each group that drives the numbering).
A common use case: finding each salesperson’s single most recent transaction. Use ROW_NUMBER, PARTITION BY salesperson, ORDER BY transaction date descending, so the most recent transaction gets row number 1 within each salesperson’s partition. Then wrap that in an outer query and filter for rows where the row number equals exactly 1, leaving precisely one row per salesperson — their most recent transaction.
This pattern — ROW_NUMBER paired with a filter for row number equals 1 — is one of the most common practical uses of window functions in business reporting. “Find the most recent X per group” or “find the highest-value X per group” comes up constantly, and it’s notably awkward to express efficiently without window functions, usually requiring far more elaborate subquery or self-join logic to land on the same result.
RANK and DENSE_RANK: Handling Ties Differently
RANK works similarly to ROW_NUMBER, also assigning a sequential position within each partition based on your ORDER BY, but it handles ties differently: rows with identical values (by whatever you’re ordering on) receive the same rank number, instead of ROW_NUMBER’s behavior of always assigning a unique number to every row regardless of ties.
When RANK gives tied rows an identical rank, it then skips the next rank number before continuing. If two rows tie for rank 1, both get rank 1, and the next distinct row gets rank 3, not rank 2 — rank 2 was effectively used up by the tie.
DENSE_RANK handles ties the same way RANK does, giving tied rows an identical value, but it never skips a subsequent rank number. In that same tie example, DENSE_RANK gives both tied rows rank 1, and the next distinct row gets rank 2 rather than rank 3, since it leaves no gaps in the overall ranking sequence.
Choosing between ROW_NUMBER, RANK, and DENSE_RANK comes down to how you want ties handled for your specific question: ROW_NUMBER if ties shouldn’t really exist conceptually or you don’t need to distinguish them, RANK if you want tied values to share a rank while preserving the gap that reflects how many rows tied at that position, or DENSE_RANK if you want that shared rank without the gap in the sequence that follows.
LAG and LEAD: Comparing a Row to the Previous or Next Row
LAG and LEAD let you pull a value from a different row relative to your current one, within the same partition and order, which is especially handy for comparing a row directly against the one immediately before or after it in your specified sequence.
LAG retrieves a value from a previous row, looking backward through your specified order; LEAD retrieves a value from a following row, looking forward. A typical use case: calculating month-over-month change in sales, where you need each month’s total alongside the immediately preceding month’s total, specifically so you can calculate the difference or percentage change between them.
Using LAG with a partition on some grouping category (say, product line) and an order by month, you can pull the previous month’s value directly alongside the current month’s row, then subtract one from the other in that same query — calculating month-over-month change directly in SQL instead of exporting the data and doing that comparison manually somewhere else.
A Complete Worked Example
Bringing several of these ideas together: “For each product category, show every individual product, that product’s sales, and what rank that product holds within its own category based on sales, from highest to lowest.”
That requires: SELECT the category column, the product name column, the sales amount column, plus RANK, OVER, PARTITION BY category, ORDER BY sales amount descending, aliased as something like “category_rank.” FROM your products or sales table.
This single query shows every product with its own sales figure clearly visible, while simultaneously showing where that product ranks against others only within its own category — all in one execution, through one window function, rather than needing separate queries per category or noticeably more complex subquery logic to reach the same combined result.
When to Reach for a Window Function vs GROUP BY
If your question needs a single summary row per group, with the individual row-level detail no longer needed in the output, GROUP BY remains the simpler and more directly appropriate tool — reaching for a window function here just adds complexity without any real payoff.
If your question needs both the row-level detail and a value calculated from that row’s broader group, visible together in the same row of output — finding the top result within each group while still seeing every row, comparing a row to an adjacent one in some sequence, or showing a running or group total alongside every transaction — a window function is almost certainly the better-fitting tool for that kind of combined need.
The Investment This Concept Deserves
Window functions intimidated me because I ran into the syntax before I’d built the simpler conceptual picture this tutorial has tried to lay out. Once “calculate across a related group of rows while still keeping every individual row visible” replaced “memorize this PARTITION BY and OVER pattern” as my actual mental model, the syntax details turned into something I could reason through and predict, rather than something I had to memorize by repetition — the same shift that eventually happened with JOINs and GROUP BY earlier in this learning progression.
What business question are you trying to answer — do you need a value calculated per group while still seeing individual rows, a ranking within groups, or a comparison to an adjacent row in some sequence? Describe your situation and I can help you build the exact window function for it.