SQL Window Functions

GROUP BY collapses rows into one per group. A window function computes a value across related rows but keeps every row. Here is the same average, taken both ways.

select category_id,
       round(avg(price), 2) as avg_price
from products
group by category_id
order by category_id

Eight categories, eight rows, and the products are gone. The window version keeps them:

select product_name,
       category_id,
       price,
       round(avg(price) over (partition by category_id), 2) as category_avg
from products
order by category_id, price desc

All 24 products come back, each beside its category's average. That's what ranking needs: every product, plus a number worked out from the products around it.

Read the window in two parts

The function says what to calculate. The OVER clause says which rows the calculation can see. PARTITION BY splits the rows into groups and restarts the calculation for each one, and ORDER BY inside the window decides the sequence, which is what numbering depends on. An empty OVER () treats the whole result as one window.

select team,
       player,
       points,
       row_number() over (
         partition by team
         order by points desc, player
       ) as team_rank
from roster

row_number() numbers the rows 1, 2, 3 within each partition. The player tie-breaker matters: without it, two players with the same points are numbered in whatever order the engine happens to read them.

When windows run

Windows are calculated after WHERE, GROUP BY, and HAVING, and before the final ORDER BY and LIMIT. Two things follow. A window sees only the rows that survived the filter, and you can't use a window in WHERE, because its value doesn't exist yet when the filter runs. Try it and you get the window functions are not allowed in WHERE error. Lesson 11.6 · Filtering windows with QUALIFY shows how to filter on one.

Ties: row_number, rank, and dense_rank

Three functions number rows, and they only disagree when two rows tie. This query ranks a handful of products by price:

select product_name,
       price,
       row_number() over (order by price desc, product_name) as row_num,
       rank() over (order by price desc) as price_rank,
       dense_rank() over (order by price desc) as dense_price_rank
from products
where price between 7.5 and 9.5
order by price desc, product_name

Cactus Mix Soil and Orchid Food both cost $9. row_number() still gives them 2 and 3, using product_name to decide. rank() gives both 2 and skips to 4 for the next price, the way a race does. dense_rank() gives both 2 and carries on at 3, so "the second-highest price" means the second distinct price. Use row_number() when you need exactly one row per position, and decide what a tie should mean before you pick either of the others. Lesson 19.1 · Ranking and ties practices all three on real ties, and the rest of Chapter 19 covers LAG, LEAD, running totals, and frames.

A good check is the row count: a window keeps the original grain unless you also add a filter. In the exercise below, all 24 products remain, and each category starts again at 1.