SQL Basics Part 5: Window Functions, Running Totals, Ranking, LAG/LEAD, and PARTITION BY

Back in Part 2, I ran into the limits of ORDER BY and LIMIT when trying to get a “top N per category” result. After covering joins in Part 3 and column transformations in Part 4, Part 5 is the topic I was most looking forward to: SQL Window Functions.

Window functions are the exact tool designed to solve problems like running totals, moving averages, ranking rows inside individual groups, and comparing a row against the previous or next row without losing the underlying row details.

What Makes a Window Function Different from GROUP BY?

The simplest way to think about a window function is: it computes an aggregate or ranking across a set of rows without collapsing those rows into a single summary row.

Comparing a standard aggregate function against a window function side by side is what made the concept click for me.

1. A standard aggregate function collapses all rows into one

SELECT AVG(unit_price) FROM dim_product;

No matter how many hundreds of products exist in dim_product, this query collapses the entire table and returns one single row containing a single average number. Every individual product row disappears into that summary value.

2. A window function keeps every row and adds a calculated column beside it

SELECT *, AVG(unit_price) OVER (ORDER BY launch_date) 
FROM dim_product;

This query still returns every single product row in dim_product. Nothing gets collapsed. Instead, the OVER (...) clause tells SQL to open a “window” of rows and attach the calculated value as an extra column right next to each product’s existing columns.

One important detail to notice here: because we included ORDER BY launch_date inside OVER (ORDER BY launch_date), SQL does not repeat the overall grand average on every row. Putting ORDER BY inside OVER() turns the calculation into a running average from the earliest launch_date up through the current row. If you want the overall table average repeated on every row, you simply use an empty OVER () clause with no ORDER BY inside it.

Window Frames: Controlling Which Rows Are Included (ROWS BETWEEN)

A window frame defines the exact slice of rows the window function should look at relative to the current row. This is controlled using ROWS BETWEEN <start> AND <end>.

Running Total (Cumulative Sum Up to the Current Row)

SELECT *, SUM(unit_price) OVER(
    ORDER BY launch_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) 
FROM dim_product;

Breaking down the frame keywords:

• UNBOUNDED PRECEDING means: start at the very first row of the ordered partition.
• CURRENT ROW means: stop at the exact row currently being evaluated.

For each product, SQL sums unit_price from row 1 up through the current row, giving you a running total that grows row by row as launch_date moves forward.

(Note: When you write OVER (ORDER BY launch_date) without specifying ROWS BETWEEN, SQL actually defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which groups tied launch_date values together at the same step. Writing ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly forces a strict row-by-row cumulative sum even when two products share the exact same launch_date.)

Grand Total Repeated on Every Row

SELECT *, SUM(unit_price) OVER(
    ORDER BY launch_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS 'total_sum'
FROM dim_product;

Changing the end boundary from CURRENT ROW to UNBOUNDED FOLLOWING tells SQL to include every row from the very first row all the way to the very last row, regardless of which row you are currently on.

This puts the grand total across the entire table onto every single row. That is super useful whenever you want to compare an individual row’s unit_price against the whole table’s total_sum (for example, calculating each product’s percentage of total value) without needing a separate query or join.

Ranking Rows: ROW_NUMBER(), RANK(), and DENSE_RANK()

All three of these ranking functions number rows based on the ORDER BY inside OVER(...), but they handle ties differently:

SELECT unit_price,
    ROW_NUMBER() OVER(ORDER BY unit_price) AS 'row_number',
    RANK() OVER(ORDER BY unit_price) AS 'rank', 
    DENSE_RANK() OVER(ORDER BY unit_price) AS 'dense_rank' 
FROM dim_product;

Suppose three products have unit_price values of 100, 100, and 150. Here is how each function numbers those three rows:

unit_price	row_number	rank	dense_rank
100	1	1	1
100	2	1	1
150	3	3	2

• ROW_NUMBER() always assigns a unique, strictly increasing integer (1, 2, 3). Even when two rows have the exact same unit_price of 100, ROW_NUMBER() arbitrarily breaks the tie so no two rows ever share a number.
• RANK() gives tied rows the exact same rank (1 and 1), and then skips ahead based on how many rows tied. Because two rows tied for rank 1, rank 2 is skipped and the next price (150) gets rank 3 (just like Olympic medals when two athletes tie for gold and the next athlete gets bronze).
• DENSE_RANK() also gives tied rows the same rank (1 and 1), but never skips numbers afterward. The next distinct price (150) gets rank 2 with zero gaps.

The easiest way I remember the three: RANK() leaves a gap after a tie, DENSE_RANK() leaves no gap, and ROW_NUMBER() ignores ties completely.

PARTITION BY: Resetting the Window Per Group

In all the queries above, the window spanned the entire dim_product table at once. Adding PARTITION BY inside OVER(...) splits the table into separate independent buckets and restarts the window calculation from scratch for each group:

SELECT unit_price, category,
    ROW_NUMBER() OVER(PARTITION BY category ORDER BY unit_price) AS 'row_number',
    RANK() OVER(PARTITION BY category ORDER BY unit_price) AS 'rank', 
    DENSE_RANK() OVER(PARTITION BY category ORDER BY unit_price) AS 'dense_rank' 
FROM dim_product;

Without PARTITION BY category, every product in the table competes in one giant ranking list. With PARTITION BY category, the ranking resets back to 1 every time a new category begins:

• The cheapest item in 'Clothing' gets row_number = 1.
• Separately, the cheapest item in 'Footwear' also gets row_number = 1.

This is the exact building block that was missing back in Part 2 when plain ORDER BY and LIMIT could only return the top N rows across the whole table. With PARTITION BY category, each category gets its own ranking from 1 upward, so you can wrap this query in a subquery or CTE and filter WHERE dense_rank <= 3 to get the top 3 products inside every single category.

Looking at Previous and Next Rows with LAG() and LEAD()

Another problem that used to feel awkward in SQL is comparing a row against the row right above or below it. That is what LAG() and LEAD() are built for:

• LAG(column) reaches back and grabs the value from the previous row in the window order.
• LEAD(column) looks ahead and grabs the value from the next row in the window order.

SELECT
    product_name,
    launch_date,
    unit_price,
    LAG(unit_price) OVER (ORDER BY launch_date) AS previous_price,
    LEAD(unit_price) OVER (ORDER BY launch_date) AS next_price
FROM dim_product;

Suppose dim_product has these three rows ordered by launch_date:

launch_date	unit_price
Jan 1	100
Feb 1	120
Mar 1	150

Running the query above produces:

launch_date	unit_price	previous_price	next_price
Jan 1	100	NULL	120
Feb 1	120	100	150
Mar 1	150	120	NULL

Notice where the NULL values show up:

• On Jan 1 (the very first row), there is no earlier row to look back at, so LAG(unit_price) returns NULL, while LEAD(unit_price) looks ahead to Feb 1 and returns 120.
• On Feb 1, LAG(unit_price) looks back at Jan 1 (100) and LEAD(unit_price) looks ahead to Mar 1 (150).
• On Mar 1 (the last row), there is no row after it, so LEAD(unit_price) returns NULL.

Calculating Row-Over-Row Price Change

Where LAG() really shines is computing differences between consecutive rows, such as how much the price changed compared to the previous launch:

SELECT
    product_name,
    unit_price,
    LAG(unit_price) OVER (ORDER BY launch_date) AS previous_price,
    unit_price - LAG(unit_price) OVER (ORDER BY launch_date) AS price_change
FROM dim_product;

Because LAG(unit_price) OVER (ORDER BY launch_date) brings the previous row’s price onto the current row, subtracting it from unit_price (120 - 100 = 20, 150 - 120 = 30) gives you the exact row-over-row change right inside SELECT.

Looking Back or Ahead More Than One Row

By default, LAG() and LEAD() step 1 row back or 1 row forward. If you pass a second argument (an offset), you can jump multiple rows back or ahead:

-- Look 2 rows back
LAG(unit_price, 2) OVER (ORDER BY launch_date)

-- Look 2 rows ahead
LEAD(unit_price, 2) OVER (ORDER BY launch_date)

For example, on the Mar 1 row (150), LAG(unit_price, 2) OVER (ORDER BY launch_date) skips over Feb 1 and reaches two rows back to Jan 1, returning 100.

Coming Up in Part 6

Window functions opened up a whole new side of SQL for me: seeing a running total build row by row, watching ranks reset per category, and comparing consecutive rows with LAG() and LEAD() makes SQL feel much closer to real data analysis than simple spreadsheet filtering.

Next up in SQL Basics Part 6: Subqueries and CTEs (Common Table Expressions), I cover writing queries inside WHERE and FROM, and chaining multi-step queries cleanly with the WITH clause (including filtering directly on the window function columns we built here).