Window Functions Reading

Window Functions, Reading Not Writing

The one idea: A window function calculates across rows without collapsing them, so unlike GROUP BY it never changes how many rows come back. Running totals, rankings, and "percent of total" columns are almost always built this way, so knowing that one difference is what lets you read them.

⚠️ The trap: The query put a region_revenue total on every row, so if I sum that column in my spreadsheet I'll get the company total. That feels right and it's wrong. A window function writes the same group total onto every row of the group, so re-adding it counts each total once per row — it's an annotation to read off a single row, not a column to sum.

Window functions are where most decision-makers quietly stop reading a query and start trusting it. They look intimidating — OVER, PARTITION BY, a second ORDER BY living inside parentheses — but they do one conceptually simple thing: they let a query compute something across rows without collapsing those rows. If you can read a GROUP BY, you can read a window function. And you need to, because running totals, rankings, and "% of total" columns in dashboards are almost always built with them.

The one-sentence definition

A window function performs a calculation across a set of rows (the "window") and writes the answer onto every row — without merging rows together.

That last part is the whole trick. Compare:

-- GROUP BY: collapses. One row per region.
SELECT region, SUM(revenue) AS region_revenue
FROM sales
GROUP BY region

-- Window function: does NOT collapse. Every sale row survives,
-- each…