Visualize how calculations are performed across a set of table rows that are somehow related to the current row.
Overview
Quick Context
Window functions are a powerful feature in SQL that perform a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions (SUM, COUNT), which collapse rows into a single output row, window functions return a value for every single row.
Data Preview (Source + Result)
READY
Unlocking SQL Window Functions
Go beyond `GROUP BY` to perform calculations across sets of rows while keeping the original rows intact. This guide will make you an expert.
The Core Idea: A "Window" into Your Data
The magic of window functions is the OVER() clause. This clause defines the "window" or set of rows the function should consider for its calculation. It has two key components:
PARTITION BY: This divides the rows into groups, or "partitions". The window function is applied independently to each partition. Think of it like a temporary GROUP BY that doesn't collapse the rows.
ORDER BY: This sorts the rows within each partition. This is crucial for functions that depend on order, like RANK(), LAG(), and LEAD().
For each row, the function calculates its result based on the other rows in its "window" (its partition).
Aggregate without collapsing
GROUP BY answers "what is the total per region?" and destroys the individual rows in the process. A window function answers "what is the total for this row's region, shown next to this row?" and keeps everything.
That is the entire distinction, and it is why window functions feel like a superpower the first time they solve a problem that previously needed a self-join.
SELECT name,
region,
amount,
SUM(amount) OVER (PARTITION BY region) AS region_total,
amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;
Every original row survives, and each one now carries its region's total and its own share of it. Doing this with GROUP BY requires computing the totals separately and joining them back — two passes and a join, replaced by one clause.
The OVER (...) is what makes a function a window function. Without it, SUM(amount) is an ordinary aggregate; with it, the same SUM is computed over a window of rows defined relative to the current row.
Reading the OVER clause
OVER has three optional parts, and each does one job.
PARTITION BY divides the rows into groups, and the window function restarts for each. It is GROUP BY for windows — without it, the whole result set is one window.
ORDER BY sorts the rows within each partition. It matters for two reasons: ranking functions need an order to rank by, and adding ORDER BY to an aggregate silently changes its frame to "everything up to and including this row", which is how running totals happen.
The frame clause — ROWS BETWEEN ... AND ... — sets exactly which rows around the current one are included. Defaults cover most cases, so this is the part you can ignore until you need a moving average.
SELECT sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total,
AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7day
FROM sales;
The first is a running total, because ORDER BY without a frame means "from the start of the partition to the current row". The second is a seven-day moving average, because the frame is stated explicitly.
One subtlety worth knowing: the default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE groups tied values together. If two sales share a date, both get the same running total — the total including both. Use ROWS instead when you want strict row-by-row behaviour.
The three ranking functions, and their differences
Given scores 90, 85, 85, 70:
Score
ROW_NUMBER()
RANK()
DENSE_RANK()
90
1
1
1
85
2
2
2
85
3
2
2
70
4
4
3
ROW_NUMBER() always gives distinct numbers, breaking ties arbitrarily. Use it when you need exactly one row per group — deduplication, "the latest record per customer".
RANK() gives ties the same rank and then skips — two second places, no third. This is how sports rankings work.
DENSE_RANK() gives ties the same rank and does not skip. Use it when you want "the top 3 distinct salaries" rather than the top 3 rows.
The deduplication pattern is worth memorising, because it comes up constantly:
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
)
SELECT * FROM ranked WHERE rn = 1; -- the most recent order per customer
Note the CTE. Window functions are computed after WHERE, so you cannot filter on rn in the same query level — it does not exist yet. Wrapping in a CTE or subquery is not a style choice; it is required.
LAG, LEAD, and comparing a row to its neighbours
LAG reaches backwards and LEAD reaches forwards within the partition, which makes period-over-period comparisons a single expression:
SELECT month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
revenue - LAG(revenue) OVER (ORDER BY month) AS change,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month), 1) AS pct_change
FROM monthly_revenue;
The first row's LAG is NULL, because there is nothing before it — supply a default with LAG(revenue, 1, 0) if a zero is more convenient than a NULL.
Related functions in the same family: FIRST_VALUE and LAST_VALUE return the first or last row in the frame, NTH_VALUE returns a specific position, and NTILE(4) splits each partition into four roughly equal buckets, which is how quartiles are computed.
LAST_VALUE has a famous gotcha: with the default frame it means "last row up to the current row", which is the current row itself. To get the genuine last value of the partition you must state the frame: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
Aggregating without collapsing the rows
A window function computes an aggregate for each row while leaving the row where it is. That one difference from GROUP BY is what makes running totals, rankings and per-group comparisons expressible in a single query.
query.sqlSQLite
-- GROUP BY collapses. A window function does not.
-- Every order stays, and each one gains its customer's totals beside it.
SELECT c.name AS customer,
o.id AS order_id,
o.placed,
count(*) OVER (PARTITION BY c.id) AS their_orders,
row_number() OVER (PARTITION BY c.id ORDER BY o.placed) AS nth,
min(o.placed) OVER (PARTITION BY c.id) AS first_order
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.placed;
-- PARTITION BY is the GROUP BY of the window: it says which rows are in
-- the same bucket. ORDER BY inside OVER decides what "running" means.
--
-- A running total needs both, plus a frame -- the default frame for an
-- ordered window is everything from the start of the partition up to the
-- current row, which is exactly a running total.
SELECT o.id,
o.placed,
count(*) OVER (ORDER BY o.placed) AS orders_so_far
FROM orders o
ORDER BY o.placed;
-- And the three ranking functions differ only in how they treat ties.
SELECT p.name,
p.price,
row_number() OVER (ORDER BY p.category) AS row_number,
rank() OVER (ORDER BY p.category) AS rank,
dense_rank() OVER (ORDER BY p.category) AS dense_rank
FROM products p
ORDER BY p.category, p.name;
-- Read the last three columns down the page. row_number is 1..5 and
-- never repeats. rank gives the two furniture rows 2 and 2, then jumps
-- to 4 -- it counts how many rows came before. dense_rank gives 2 and 2
-- and then 3 -- it counts how many distinct values came before.
-- Pick the one whose tie-breaking you actually want; they only differ
-- when there are ties, which is exactly when it matters.
Result
Things to try
The visualizer helps you see how the PARTITION BY and ORDER BY clauses define the calculation for each row.
Ranking Sales within Departments:
Use the default 'Sales' dataset.
Select the RANK() function.
Partition ByDept.
Order BySales (DESC).
Click Run Visualizer. The animation first sorts the data by Department, then by Sales. It then processes each partition ('HR', 'Sales') separately, assigning a rank to each employee based on their sales within their own department.
RANK() vs. DENSE_RANK():
In the 'Sales' data, notice that 'David' and 'Eve' in HR have the same sales (5000). With RANK(), they both get rank 1. The next person, 'Frank', gets rank 3 because two people tied for first.
Now, switch the function to DENSE_RANK() and run it again. David and Eve still get rank 1, but Frank now gets rank 2. DENSE_RANK doesn't skip numbers after a tie.
Finding the Previous Sale (LAG):
Keep the same partition and order, but select the LAG(Value) function.
Run the visualizer. For each row, the result is the Sales value from the row directly above it *within the same partition*. The first row of each partition (e.g., Bob in Sales, Frank in HR) will have a NULL result because there is no preceding row in their window.
Common Use Cases
Window functions are essential for advanced analysis. Here are some classic problems they solve:
-- Find the top 3 selling employees in each region
SELECT * FROM (
SELECT Name, Region, Sales,
RANK() OVER (PARTITION BY Region ORDER BY Sales DESC) as rank_num
FROM SalesData
) AS ranked_sales
WHERE rank_num <= 3;
-- Calculate month-over-month sales growth for each department
SELECT Dept, Month, Sales,
Sales - LAG(Sales, 1, 0) OVER (PARTITION BY Dept ORDER BY Month) as sales_growth
FROM MonthlySales;
The short of it
Window functions compute a value for each row based on a "window" of related rows.
They do not collapse rows, preserving the original table's granularity.
PARTITION BY is like a temporary GROUP BY that defines the window.
ORDER BY is essential for ranking and sequential functions (LAG, LEAD).
Use them for ranking, running totals, moving averages, and comparing a row to its peers.
Where they are used in real reporting
Running totals and cumulative sums — account balances, year-to-date revenue, inventory levels over time.
Moving averages — smoothing noisy daily figures into a readable trend.
Top-N per group — the three best-selling products in each category, in one query rather than one query per category.
Deduplication — keeping the newest row per key when a source system sends repeats.
Gap and island detection — finding consecutive runs, such as how many days in a row a user was active, using the difference between a date and a ROW_NUMBER() over that date.
Percentages of a total — each row's share of its group, without a self-join.
Sessionisation — grouping events into sessions by comparing each event's timestamp with the previous one via LAG.
Almost all of these have a pre-window-function equivalent involving correlated subqueries or self-joins, and almost all of those are both slower and considerably harder to read.
Performance and ordering
Window functions run late in the pipeline — after WHERE, GROUP BY and HAVING, and before ORDER BY and LIMIT. Three consequences follow:
You cannot filter on a window function's result in the same query level. Wrap it.
Filtering rows in WHERE happens before the window is computed, so it changes the totals. That is usually what you want, but it means a running total over a filtered set is a running total of the filtered rows only.
The window sees rows after grouping, so you can combine GROUP BY and window functions — SUM(SUM(amount)) OVER (...) is legal and computes a window over the grouped results.
For performance, each distinct OVER specification generally requires its own sort. Reusing the same partition and ordering across several functions lets the engine sort once and compute them all together, so keep the specifications identical where possible — or name them once with a WINDOW clause:
SELECT name,
RANK() OVER w,
SUM(amount) OVER w
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY amount DESC);
An index on the partition and ordering columns can remove the sort entirely, which is the main lever available on large tables.
Questions people ask
Do all databases support window functions? Yes, in current versions — PostgreSQL for many years, MySQL since 8.0, SQLite since 3.25, SQL Server and Oracle long before that. Only genuinely old versions lack them.
Can I use DISTINCT inside a window function? Not portably. PostgreSQL and most engines reject COUNT(DISTINCT x) OVER (...). The usual workaround is a DENSE_RANK() trick or a pre-aggregating CTE.
What is the difference from a correlated subquery? They can express many of the same things, but the window function is computed in a single pass over sorted data, while the subquery is potentially re-executed per row. The window version is usually much faster and always shorter.
Can I nest window functions? Not directly. Compute the inner one in a CTE and apply the outer one to its result.
Why is my running total repeating the same value for several rows? Because the default frame uses RANGE, which lumps tied ordering values together. Switch to ROWS.
Do they work with GROUP BY? Yes, and the window operates on the grouped rows. This is how "each region's share of the national total" is written in one query.
Recap in one screen
A window function computes across a set of rows without collapsing them.
OVER (PARTITION BY ... ORDER BY ... frame) defines which rows each calculation sees.
Adding ORDER BY to an aggregate turns it into a running total; state ROWS explicitly for moving windows.
ROW_NUMBER, RANK and DENSE_RANK differ only in how they treat ties.
They run after WHERE, so filtering on their output needs a CTE or subquery.
Predict, then reveal
About to run: Ranking Sales within Departments. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Recall check
0 of 3
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What does this module say about “Quick Context”?
Window functions are a powerful feature in SQL that perform a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions ( SUM , COUNT ), which collapse rows into a single output row, window functions return a value for every single row .
What does this module say about “The Core Idea: A "Window" into Your Data”?
The magic of window functions is the OVER() clause. This clause defines the "window" or set of rows the function should consider for its calculation. It has two key components:
What does this module say about “Aggregate without collapsing”?
GROUP BY answers "what is the total per region?" and destroys the individual rows in the process. A window function answers "what is the total for this row's region, shown next to this row?" and keeps everything.
Cheat sheet
SQL Window Functions
Window functions are a powerful feature in SQL that perform a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions (SUM, COUNT), which collapse rows into a single output row, window functions return a value for every single row.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.