Blog›SQL Window Functions: OVER, ROW_NUMBER and RANK
SQL SkillsCareer
Window Functions and the OVER Clause
Window functions look intimidating and are secretly simple: keep every row, and add one calculated column beside it. Switch between the functions below and watch the board change.
CR
Conor Robertson
June 22, 2026 · 10 min read
Frequently asked questions
The questions that come up most once people start writing window functions for real.
A window function performs a calculation across a set of rows that are related to the current row, without collapsing them into one. Unlike a normal aggregate, it keeps every row and adds the result as an extra column, so you can show a running total, a rank or each row compared to the previous one while still seeing all your original data. The OVER clause is what makes a function a window function.
Both give tied rows the same rank. The difference is what comes next. RANK leaves a gap: if two rows tie for 2nd, the next row is 4th. DENSE_RANK leaves no gap: after a tie for 2nd, the next row is 3rd. Use RANK for Olympic-style standings, and DENSE_RANK when you want the Nth distinct value with no holes in the numbering.
OVER is the keyword that turns an ordinary aggregate like SUM or COUNT into a window function. It tells the database to compute the value across a window of rows and write the answer next to every row, instead of collapsing them. Inside the brackets you define the window with ORDER BY, PARTITION BY, or both.
GROUP BY collapses many rows into one summary row per group, so you lose the individual rows. A window function keeps every row and adds the calculated value alongside. If you need a grand total and nothing else, GROUP BY is fine. If you need that total or rank next to each original row, you need a window function.
PARTITION BY splits the rows into groups and restarts the calculation for each one. ROW_NUMBER() OVER (PARTITION BY team ORDER BY xp DESC) numbers each team from 1 independently, rather than numbering the whole table straight through. It is GROUP BY for windows: the same grouping, but every row is kept.
Use a window function with PARTITION BY. Number the rows inside each group with ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column DESC), wrap that in a CTE or subquery, then filter the outer query to where that number is less than or equal to N. ROW_NUMBER gives exactly N rows per group; swap in RANK or DENSE_RANK if you want tied rows at the cutoff to all come through.
No. Window functions are calculated at the SELECT stage, after WHERE has already filtered the rows, so the window result does not exist yet when WHERE runs. Writing WHERE rank = 1 throws an error. The fix is to compute the window function in a CTE or subquery and filter on its result in the outer query.
Yes, in current versions. PostgreSQL has supported them for years. MySQL added them in 8.0 and SQLite in 3.25. The syntax, ROW_NUMBER(), RANK(), SUM() OVER (...) and so on, is essentially identical across all three, so what you learn here transfers directly.
The OVER clause is what turns a normal function into a window function. Written as function(...) OVER (...), it tells the database to compute the value across a window of rows and place the result beside every row instead of collapsing them. Inside the brackets you define the window with PARTITION BY for grouping and ORDER BY for ordering; leave them out and OVER () spans the whole result set. Without OVER, something like ROW_NUMBER() is not valid and SUM() is just a plain aggregate.
Yes. Window functions are standard SQL and behave the same in BigQuery, Snowflake, Redshift, SQL Server (T-SQL) and PostgreSQL, so ROW_NUMBER(), RANK() and SUM(...) OVER (...) are identical across all of them. The main difference is how you filter on the result: Snowflake, BigQuery and DuckDB offer a QUALIFY clause, while PostgreSQL, MySQL and SQL Server require you to wrap the window in a CTE or subquery first.
Ready to practice on real data?
QueryCase teaches SQL through mystery cases on real datasets. Music, sports, film, and more. Your first cases are completely free.
Chief FoxROW_NUMBER() counts straight down the order with no ties and no gaps, highest XP first. Two detectives on 3200 still get different numbers.
#detectivexpsquad
1Fox3,950Alpha
2Raven3,200Alpha
3Owliver3,200Bravo
4Sterling1,850Bravo
5Quill1,200Alpha
row_number
Hover or tap any number to see the rows it is built from.
SELECT name, xp, squad, ROW_NUMBER()OVER (ORDER BY xp DESC) AS row_number FROMdetectives;
Window functions are the wall most people hit after they are comfortable with SELECT, WHERE, JOIN and CASE. The syntax looks alien, the explanations talk about "frames" and "partitions", and it all feels like a different language. It is not.
Above is a leaderboard of five detectives, ranked by the XP they have earned closing cases. The explorer is the whole idea: pick a function, steer it with PARTITION BY and ORDER BY, and watch it add one column without throwing a single row away. Tap any number in that new column to see exactly which rows it was built from. The board still shows all five detectives, every time.
That last part is the secret. Everything you already know about SUM and COUNT still applies. A window function just keeps the rows.
What does the OVER clause do?
OVER is the keyword that turns an ordinary function like SUM, COUNT or ROW_NUMBER into a window function. It tells the database to run the calculation across a window of related rows and write the answer next to every row, instead of collapsing them into one summary. You shape that window inside the brackets with PARTITION BY (which rows to group) and ORDER BY (in what order), and you can use either, both, or neither.
In one line: SUM(xp) OVER (ORDER BY xp DESC) is a running total, and RANK() OVER (ORDER BY xp DESC) is a ranking. Same rows, one extra column, nothing thrown away. Everything below is really just that one idea, shown from a few angles.
The one idea that makes them click
The mental model
A normal aggregate collapses rows: GROUP BY crushes each group down to a single summary row and the individual names are gone. A window function does the same maths but keeps every row, writing the answer in a new column beside each one. That is the entire difference. Running totals, rankings, "this row versus the last": all of it is just an aggregate that refused to collapse.
Here is the same calculation said both ways. On the left, GROUP BY squad adds up each squad and hands back one row per squad: the names are gone. On the right, the identical sum wrapped in SUM(xp) OVER (PARTITION BY squad) writes each squad total beside every member, with all five detectives still on the board.
GROUP BY · collapses2 rows
SELECT squad, SUM(xp) AS squad_total
FROMdetectivesGROUP BY squad;
squadsquad_total
Alpha8,350
Bravo5,050
One row per squad. The five names are gone.
SUM() OVER · keeps every row5 rows
SELECT name, squad,
SUM(xp) OVER (PARTITION BY squad) AS squad_total
FROMdetectives;
namesquadsquad_total
FoxAlpha8,350
RavenAlpha8,350
OwliverBravo5,050
SterlingBravo5,050
QuillAlpha8,350
Same totals. GROUP BY returns one row per squad. The window writes each squad total beside every member, all five rows kept.
Same totals, different row counts. That gap, two rows versus five, is the entire idea. Once you see it, window functions stop being mysterious.
Why it is called a window
Picture standing on one row and looking out at the rows around it. That view, the slice of rows a single row is allowed to see, is the window. For a running total it is every row from the top down to you. For LAG it is the one row directly above. For a rank it is everyone you are competing against.
Every detective stays on the board, and the new running_total column simply grows, SUM(xp) OVER (ORDER BY xp DESC) climbing from 3,950 to 13,400 as each row's window stretches one line further down.
The calculation never leaves that window. Change the window with PARTITION BY and ORDER BY and you change what each row can see, which changes the answer. It is why the same SUM can be a grand total, a running total, or a per-group total depending only on the brackets. Tap a number back in the explorer and the window it used lights up in the table beside it.
Anatomy of a window function
Every window function reads the same way once you can name the three parts. Learn them on one expression and you can read any of them.
SUM(xp)OVER(PARTITION BY squad ORDER BY xp DESC)
SUM(xp)· The calculation, and it is one you already know. SUM, COUNT, AVG, RANK and friends all work here. Nothing new yet.
OVER· The one word that changes everything. OVER turns an aggregate into a window function, so instead of collapsing the rows it keeps every one and writes the answer alongside. This is the whole idea in a single keyword.
(PARTITION BY squad ORDER BY xp DESC)· The window: the rows the calculation can see. Two optional dials live in here. PARTITION BY groups the rows (here, by squad) and restarts the calculation for each group. ORDER BY sequences them (here, highest XP first) so a running total or rank knows what "above" means. Use either, both, or neither.
The calculation on the left is old news. The word OVER is the entire trick. The brackets on the right are where you steer it with PARTITION BY and ORDER BY: which groups, and in what order. Both are optional, which is why a window can be as short as OVER ().
The functions you'll actually use
There are only a handful worth memorising, and they split cleanly into ranking functions, running aggregates, and the row-to-row pair.
ROW_NUMBER()
1234
A unique number for every row in order. No ties, ever. The go-to for de-duplicating or grabbing the top N rows per group.
RANK()
1224
Standings that honour ties. Equal values share a rank, then the next rank skips the gap. Olympic-style scoring.
DENSE_RANK()
1223
Ties share a rank, but the next rank does not skip. Reach for it when you need the Nth distinct value with no holes.
SUM() OVER
SUMAVGCOUNTMINMAX
Any aggregate, kept per row instead of collapsed. SUM, AVG, COUNT, MIN and MAX all take OVER and work the same way: running totals, share of total, a group average printed next to every line.
LAG() / LEAD()
←now→
Reach into the previous or next row. Month-over-month change, the gap between two events, this row versus the one before it.
NTILE(n)
1234
Split ordered rows into n equal buckets. Quartiles, deciles, percentiles. NTILE(4) sorts everyone into four tiers, top to bottom.
The SUM() OVER card stands in for a whole family. AVG, COUNT, MIN and MAX all take an OVER clause and behave identically: the rows stay, and the aggregate is written beside each one. Anything you would reach for after GROUP BY works as a window function too, just without the collapse. The explorer uses SUM to keep things simple, but every one of them follows the same rules you just watched.
The three ranking functions trip people up the most, because they look interchangeable until a tie shows up. So let's put them side by side.
RANK vs DENSE_RANK
They behave identically until two rows tie, and then they part ways on the very next row. Flip between them and watch what happens to Sterling in 4th.
tie on 3200, then 4
rankdetectivexp
1Fox3,950
2Raven3,200
2Owliver3,200
4Sterling1,850
After two detectives share 2nd, RANK jumps to 4, skipping 3. The gap counts how many tied above.
ROW_NUMBER() is the third sibling: it refuses to tie at all, handing out a unique number even when the scores are identical. Reach for it when you need exactly one row per group, like the single most recent order per customer.
PARTITION BY: the same query, restarted per group
By default a window runs straight down the whole table. PARTITION BY slices it into groups and starts the calculation over for each slice.
Go back to the explorer and switch PARTITION BY from none to squad. Watch ROW_NUMBER restart at 1 for Alpha, then again for Bravo. It is GROUP BY for windows: the same grouping, but every row is kept.
While you are there, flip ORDER BY to xp ASC. The numbers change, but the rows stay exactly where they were. That is proof the ORDER BY inside OVER steers the calculation, not how the rows are listed.
The pattern you'll use constantly: top N per group
Combine PARTITION BY with ROW_NUMBER() and you get the answer to a question that comes up everywhere: the top item in each category. ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) numbers each category from 1, so filtering to where that equals 1 gives you the best seller in every category at once. Most recent order per customer, highest score per team, latest status per ticket: same shape every time.
Putting it together: the top 2 per group
Here is the question almost every window function tutorial is quietly building towards, and one of the most common SQL interview questions there is: who are the top two earners in each department? It needs a ranking, restarted per group, then a filter. That is PARTITION BY, ORDER BY, and one twist.
SQLTop 2 earners per department
WITH ranked AS (
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS dept_rank
FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE dept_rank <=2;
Read it top to bottom. ROW_NUMBER() numbers each department's salaries from highest down, PARTITION BY department restarts that count for every department, and dept_rank <= 2 keeps the top two of each. Swap department for squad and salary for xp and it is the exact query the explorer has been running.
The twist is the WITH ... AS (...) wrapper, a CTE. You cannot put WHERE dept_rank <= 2 in the same query as the window function, because the rank has not been worked out yet at the point WHERE runs. So you compute the rank in one step, then filter it in the next. This wrap-then-filter shape is behind almost every top-N-per-group query you will ever write.
Which function do I want?
Once the idea clicks, the only thing left is matching the question to the function. Read this one backwards: find the result you are after on the left, and the function is on the right.
You want
Reach for
In the wild
A unique number for every row, in order
ROW_NUMBER()
The single most recent order per customer
Standings where tied rows share a place
RANK()
A leaderboard with a joint 2nd
A ranking with no gaps after ties
DENSE_RANK()
The top 3 distinct prices
A running total down the rows
SUM(x) OVER (ORDER BY ...)
Cumulative revenue by date
Each row as a share of the whole
x / SUM(x) OVER ()
Percent of total sales
This row compared to the previous one
LAG(x)
Month-over-month change
This row compared to the next one
LEAD(x)
Days until the next visit
The same calculation, restarted per group
add PARTITION BY ...
A running total per customer
Rows split into N equal buckets
NTILE(n)
Quartiles, deciles, percentiles
OVER across Postgres, MySQL, BigQuery and SQL Server
The good news for interviews: the core is identical everywhere. ROW_NUMBER(), RANK(), DENSE_RANK() and SUM(...) OVER (PARTITION BY ... ORDER BY ...) read exactly the same in PostgreSQL, MySQL 8.0+, SQLite 3.25+, SQL Server, Snowflake, BigQuery and Redshift. Learn the shape once and it travels.
The differences are small, and worth knowing before an interview:
Filtering on a window result. The "you cannot use it in WHERE" rule holds everywhere, but Snowflake, BigQuery and DuckDB add a QUALIFY clause that filters a window result directly, so QUALIFY ROW_NUMBER() OVER (...) = 1 gets the top row per group in one statement. PostgreSQL, MySQL and SQL Server have no QUALIFY, so you wrap the window in a CTE or subquery, exactly as in the worked example above.
Older versions. MySQL only gained window functions in 8.0 and SQLite in 3.25, so a legacy MySQL 5.7 box will reject them outright. Every current cloud warehouse supports them in full.
SQL Server (T-SQL). The OVER syntax is standard, but T-SQL limits rows with TOP and OFFSET ... FETCH rather than LIMIT, and its date functions differ. The window functions themselves are unchanged.
So a RANK() OVER (ORDER BY revenue DESC) you write here runs unchanged in a BigQuery, Snowflake or SQL Server interview. Only the scaffolding around it, how you limit rows or filter the rank, moves.
Quick check
Quick check
You need the top 3 distinct scores on the board, with no gaps in the numbering even when two detectives tie. Which function do you reach for?
A couple more traps
ORDER BY inside OVER is not the same as ORDER BY at the end
The ORDER BY inside the brackets controls the calculation, deciding what counts as "above" for a running total or a rank. The ORDER BY at the end of the query only controls how the final rows are displayed. They are different jobs, and you often need both.
A running total needs an ORDER BY to mean anything
SUM(xp) OVER () with empty brackets gives every row the same grand total, which is occasionally exactly what you want, since xp / SUM(xp) OVER () is how you get each row's share of the total. To make it climb instead, you need SUM(xp) OVER (ORDER BY xp DESC). The order is what turns a single number into a progression. Set the explorer to SUM and flip ORDER BY between none and xp DESC to watch the grand total become a running total.
ROW_NUMBER breaks ties however it likes
Give two rows the same value and nothing else to go on, and the database is free to number them in either order, and it may pick differently each run. When the tiebreak matters, add a second column to the ORDER BY, like ORDER BY xp DESC, name, so the result is stable every time.
The fastest way to learn this is to write it, not read it. Reading about window functions gets you to "I follow that." Writing them, watching the extra column appear, and filtering on it gets you to "I reach for OVER without thinking." The gap is just reps: pick a question, add the window, and check the column against what you expected. The practice questions finish on window functions, the SQL cheat sheet has the syntax for every one of them, and the SQL detective game spends a whole rank here.
SQL Window Functions: OVER, ROW_NUMBER and RANK | QueryCase
