Ian Klosowicz
A window function performs a calculation across a set of rows that are related to the current row, without collapsing those rows into a single output the way GROUP BY does. You reach for one when you need a running total, a rank, a row number, or a value from a different row, while still keeping every individual row in your result.
If you've hit a wall where GROUP BY drops the detail you need, window functions are usually the answer.
Because window functions don't collapse rows, it helps to be solid on how GROUP BY aggregations behave first, including the traps that catch almost everyone.
The key difference between a window function and a regular aggregate is what happens to your rows. GROUP BY collapses them. A window function keeps every row and adds a new column with the calculation result alongside it.
Think about it this way: if you GROUP BY customer and SUM their orders, you get one row per customer. If you use SUM as a window function, you still get one row per order, but each row also has the customer's total sitting next to it. Same math, completely different shape.
That shape difference is what makes window functions useful. You can compare a row to its group without losing the row. You can rank rows within a partition. You can look one row back in time. None of that is possible with GROUP BY alone.
Every window function uses an OVER clause. That's what makes it a window function. OVER with nothing inside it applies the calculation across the entire result set. OVER with PARTITION BY splits the result into groups and applies the calculation per group. OVER with ORDER BY defines the order of rows within each window.
SELECT
customer_id,
order_date,
order_total,
SUM(order_total) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;
This gives you every order row plus a running total per customer, ordered by date. The window is "all rows for this customer up to and including the current date." Change PARTITION BY and you change what the window is. Remove it entirely and the window is the whole table.
Getting comfortable with OVER is 80% of learning window functions. The rest is just knowing which function to call.
These 3 assign a number to each row based on its position within a window. The differences are small but matter when there are ties.
SELECT
customer_id,
order_date,
order_total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders;
Filter WHERE rn = 1 and you've got the most recent order per customer. This is one of the most common patterns in analytics SQL, and it's what I use constantly at work when pulling the latest record per entity from a Snowflake table.
ROW_NUMBER is the one you'll use most. RANK and DENSE_RANK come up in leaderboards and scoring scenarios.
Standard aggregates, SUM, AVG, COUNT, MIN, MAX, all work as window functions when you add OVER. The calculation runs within the window instead of across the whole table.
SELECT
order_date,
revenue,
SUM(revenue) OVER (ORDER BY order_date) AS cumulative_revenue,
AVG(revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day_avg
FROM daily_revenue;
The ROWS BETWEEN clause controls exactly which rows are included in the window for each calculation. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means "the current row and the 6 rows before it," a 7-day rolling average. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means "everything from the start up to and including now," a cumulative total.
Running totals and moving averages show up everywhere in business reporting. Before window functions, people were doing this with correlated subqueries or in Python after pulling the data. Window functions do it in a single clean query.
LAG gives you a value from a previous row. LEAD gives you a value from a following row. Both take the column name, the number of rows to look back or forward (default is 1), and an optional default value if no row exists at that offset.
SELECT
month,
revenue,
LAG(revenue, 1) OVER (ORDER BY month) AS prior_month_revenue,
revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month_change
FROM monthly_revenue;
Month-over-month change, week-over-week growth, day-over-day delta, all of these need LAG. Without it, you're either self-joining the table or pulling the data into Python to compute the diff. LAG does it in the query itself.
LEAD works the same way in the other direction. If you want to compare each row to the next one, like checking whether a subscription is about to lapse, LEAD is what you want.
FIRST_VALUE returns the value from the first row of the window. LAST_VALUE returns the value from the last row. These are useful when you want to compare every row in a group against the baseline or endpoint of that group.
SELECT
customer_id,
order_date,
order_total,
FIRST_VALUE(order_total) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order_total
FROM orders;
Every order now has the customer's very first order total next to it. You can compute how much the customer has grown, whether they're ordering more or less than they started, or flag customers whose first order was unusually large.
LAST_VALUE has a catch: by default, the window frame ends at the current row, so LAST_VALUE just gives you the current row's value. To get the actual last row in the partition, you need to explicitly set the frame to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. It trips people up the first time. Now you know.
Window functions are the right tool when you need any of the following things that GROUP BY can't give you without losing row-level detail:
I've heard from a lot of aspiring analysts who study window functions and then freeze up when the interviewer asks them to write one on the spot. The gap is almost always the same thing: they've read about OVER but haven't written enough queries to have a pattern in their head. The fix is 10 or 15 practice queries on real data, not more reading.
Window functions come up in take-home assessments more than almost any other SQL topic. If you're interviewing for an analyst role and you can't write a ROW_NUMBER or a LAG from memory, that's the thing to fix first. The Analyst Hive program builds this kind of pattern fluency into a daily structure, including the exact SQL questions that show up in analyst interviews.
One more thing: window functions run after WHERE, GROUP BY, and HAVING but before SELECT's final output. That means you can't filter on a window function result directly in a WHERE clause. Wrap it in a subquery or a CTE first.
-- This fails:
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
WHERE rn = 1;
-- This works:
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;
That CTE pattern is the single most-used window function pattern in day-to-day analytics work. Write it enough times and it becomes automatic.
Before going deep here, it helps to know how much SQL an entry-level role expects, since window functions are often optional at that stage.
What's the difference between a window function and GROUP BY?
GROUP BY collapses rows into one row per group. A window function keeps every row intact and adds a calculated column alongside it. Both can compute aggregates like SUM or AVG, but window functions let you access the detail rows while still doing group-level math.
Do window functions slow down my queries?
They can add compute, especially on large tables with no partitioning. On cloud warehouses like Snowflake or BigQuery, the query optimizer handles them well. On smaller on-prem databases, a complex window with a large frame can be slow. Test with EXPLAIN before running on millions of rows without filters.
Can I use multiple window functions in one query?
Yes, and you often will. Each window function in the SELECT list evaluates independently. You can mix ROW_NUMBER, LAG, and SUM in the same query with different OVER clauses. Keep in mind that each one is a separate pass over the data, so more window functions means more compute.
What does PARTITION BY do?
PARTITION BY divides the result set into groups before applying the window function. Think of it as the window-function version of GROUP BY, but instead of collapsing rows, it just defines the scope of the calculation. Without PARTITION BY, the window spans the entire result set.
When should I use ROW_NUMBER vs RANK?
ROW_NUMBER when you need a unique number per row regardless of ties, like grabbing the single most recent row per customer. RANK when ties deserve the same position and you're fine with gaps in the sequence. DENSE_RANK when ties deserve the same position but you want consecutive numbers with no gaps.
Why isn't my LAST_VALUE returning the last row?
Because the default window frame ends at the current row. LAST_VALUE always returns the current row's value under the default frame. Fix it by adding ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to your OVER clause, which tells SQL to look all the way to the end of the partition.
Window functions are one of those SQL topics where 1 hour of practice on a real dataset does more than 5 hours of reading. Pick any table with timestamps and an ID column, try writing a running total, a ROW_NUMBER filter, and a LAG comparison, and the concepts will click fast.
If you're building toward an analyst role and want a structured daily plan for exactly this kind of SQL practice, Analyst Hive is built around it. The community is at skool.com/analysthive/about.
