Window Functions

A window function calculates across a set of rows related to the current row — without collapsing them into one summary row the way GROUP BY does. This is one of the most powerful, and most underused, features in SQL for anyone coming from a beginner-level background.

Ranking rows

SELECT name, course, score,
       RANK() OVER (PARTITION BY course ORDER BY score DESC) AS rank_in_course
FROM students;

PARTITION BY divides the rows into groups, just like GROUP BY would — but unlike GROUP BY, every individual row is still returned, each one now carrying its rank within its own group. This gives every student a rank within their own course, while still showing every single row, something a plain GROUP BY simply cannot do in one query.

Running totals

SELECT name, score,
       SUM(score) OVER (ORDER BY name) AS running_total
FROM students;

A running total, running average, or running count over an ordered sequence of rows is a classic window function use case — the kind of calculation that’s genuinely painful to write with plain GROUP BY and joins, but reads almost naturally once you know the OVER (...) syntax.

ROW_NUMBER vs RANK vs DENSE_RANK

SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()       OVER (ORDER BY score DESC) AS rank_num,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_num
FROM students;

ROW_NUMBER() always gives unique numbers, even for tied scores; RANK() gives tied rows the same number and then skips the next rank (1, 1, 3); DENSE_RANK() gives ties the same number without skipping (1, 1, 2). Pick based on whether gaps in the ranking should be allowed for your specific use case.