Window Functions: Calculations Across Rows Without Collapsing Them
The one capability GROUP BY fundamentally can't offer — a per-row calculation that sees other rows in its group without collapsing the result down to one row per group.
What you'll learn
- Explain how a window function differs fundamentally from GROUP BY aggregation
- Use ROW_NUMBER() and RANK() with PARTITION BY and ORDER BY
- Use a window function to compute a running total or per-group rank without collapsing rows
Prerequisites
Explanation
This lesson's exercises run in this sandbox's real SQL runner, which is SQLite, not PostgreSQL. Standard OVER()/PARTITION BY/ORDER BY window-function syntax is genuinely dialect-compatible between the two, so what you build and run here behaves the same way in a real PostgreSQL database.
GROUP BY fundamentally collapses many rows into one summary row per group — you lose the individual rows, keeping only the aggregate. A window function does something genuinely different: it computes a value across a group of related rows (a "window") while keeping every individual row in the result. SELECT title, price, AVG(price) OVER () AS overall_avg FROM books returns every single book row, each annotated with the same overall average price alongside it — something GROUP BY cannot do at all without a separate subquery joined back in, precisely because grouping and "keep every row" are structurally incompatible with each other.
The OVER (...) clause defines the window. PARTITION BY genre divides rows into groups (partitions) the way GROUP BY would, but again, without collapsing anything — each row still appears individually, now annotated with a value computed within its own partition. ORDER BY price DESC inside the same OVER (...) clause additionally defines a per-partition ordering, which specific window functions use directly: ROW_NUMBER() OVER (PARTITION BY genre ORDER BY price DESC) assigns each book a unique, sequential rank within its own genre, ordered by price — "the 1st, 2nd, 3rd most expensive book in Fiction," independently restarting the count for Nonfiction.
ROW_NUMBER() always assigns strictly sequential, unique numbers (1, 2, 3, 4...) even when values tie — ties are broken arbitrarily (by row order) unless the ORDER BY fully disambiguates them. RANK(), by contrast, gives tied rows the same rank, then skips the following rank number(s) accordingly (1, 2, 2, 4 — note there's no 3, since two rows tied for 2nd). SUM(price) OVER (ORDER BY order_date) (with no PARTITION BY) computes a running total — each row's window is "every row up to and including this one," in the specified order — a genuinely different, common calculation (a running balance, a cumulative count) that would otherwise require a self-join or a correlated subquery to express without window functions.
Example
PARTITION BY and ROW_NUMBER(), genuinely running here -- standard window-function syntax, dialect-compatible with PostgreSQL.
-- Rank each book by price WITHIN its own genre, without collapsing any rows:
SELECT
title,
genre,
price,
ROW_NUMBER() OVER (PARTITION BY genre ORDER BY price DESC) AS rank_in_genre
FROM books
ORDER BY genre, rank_in_genre;Try it yourself
Change ROW_NUMBER() to RANK() and compare how tied prices (if any) are handled differently.
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Guided exercise
Guided exercise
Write a query returning every book's title, price, and the AVERAGE price across ALL books (using AVG(...) OVER (), no PARTITION BY) alongside it -- every row should show the same overall average, with no rows collapsed.
Checks: Returns every book row individually, each annotated with the same overall average price
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Independent exercise
Independent exercise
Write a query returning each book's title, genre, price, and its RANK() within its own genre by price descending (most expensive = rank 1), ordered by genre then rank.
Checks: Correctly ranks each book within its own genre by price, with all rows preserved
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Common mistakes
- Trying to use GROUP BY to get 'each row plus a group-level statistic' -- GROUP BY structurally collapses rows; only a window function (or a GROUP BY subquery joined back to the original rows) can annotate every individual row with a group-level value.
- Using ROW_NUMBER() when RANK() is actually needed (or vice versa) -- ROW_NUMBER() always gives unique sequential numbers even for ties, which silently breaks a tie arbitrarily; RANK() correctly gives tied rows the same rank, which matters whenever ties are meaningful to the result (a leaderboard, for instance).
- Forgetting PARTITION BY when a per-group calculation is intended -- OVER (ORDER BY price) with no PARTITION BY computes across the ENTIRE result set, not per group, which silently produces a running total or rank spanning every group combined instead of resetting per group.
Knowledge check
Takeaway
A window function computes a group-aware value while preserving every individual row — the one thing GROUP BY structurally cannot do — with PARTITION BY defining independent groups and ORDER BY (inside OVER) defining the per-group sequence that ROW_NUMBER, RANK, and running totals rely on.
Summary
Window functions (OVER (...)) compute across related rows without collapsing them, unlike GROUP BY. PARTITION BY divides rows into independent groups; ORDER BY inside OVER defines per-group sequence. ROW_NUMBER() gives unique sequential numbers even for ties; RANK() gives tied rows the same rank and skips ahead. A running total uses SUM(...) OVER (ORDER BY ...) with no PARTITION BY.
References
Your notes
Notes save automatically.
Finished this lesson?
Mark it complete to track your progress and schedule a future review.
AI tutor
The optional AI tutor isn't enabled in this deployment. All lessons, exercises, quizzes, and search work fully without it.