A window: GROUP BY that keeps the rows

The question GROUP BY cannot answer

“Show every order, and next to it, the share of its city’s revenue that it represents.” GROUP BY can give you the city totals. It cannot give you the orders, because it has collapsed them into the totals. You could compute the totals in a CTE and join them back, which works and which you have done in earlier lessons. A window function does the same thing in one expression, and once you can read one, most of the awkward SQL you meet turns out to be somebody working around not knowing it.

SELECT order_id, city, amount,
       sum(amount) OVER (PARTITION BY city)             AS city_total,
       amount / sum(amount) OVER (PARTITION BY city)    AS share_of_city
FROM orders;

Every row stays. Each one gains a column that looks at the other rows in its city, adds them up, and writes the answer beside it. OVER is the word that turns an aggregate into a window function. PARTITION BY city says which rows count as “the others”: the window a row looks through is its own partition.

OVER () with nothing inside means the window is the whole result. amount / sum(amount) OVER () is each order’s share of everything.

Three parts, in order

A window has up to three parts, and the later lessons in this module are each about one of them.

• PARTITION BY decides which rows a row can see. Like GROUP BY, without the collapsing.
• ORDER BY, inside the OVER, puts the visible rows in sequence, which is what “previous row”, “rank” and “running total” need. Without it there is no previous row.
• The frame narrows the sequence further, to “the six rows before this one” or “everything up to here”. It only exists once there is an ORDER BY, and it has a default that surprises people, which gets its own lesson.

When it runs, and why that matters

A window function is computed after WHERE, GROUP BY and HAVING have finished and before ORDER BY and LIMIT. That order explains the first error everybody hits:

SELECT *, row_number() OVER (ORDER BY amount DESC) AS rn
FROM orders
WHERE rn <= 10;
-- ERROR: column "rn" does not exist

WHERE runs before the window is computed, so the column is not there yet. The fix is to compute in one layer and filter in the next:

WITH ranked AS (
  SELECT *, row_number() OVER (ORDER BY amount DESC) AS rn FROM orders
)
SELECT * FROM ranked WHERE rn <= 10;

The same order explains a quieter fact: a window function sees the rows after WHERE has applied. sum(amount) OVER () in a query with WHERE city = 'Pune' is Pune’s total, not the company’s. If you need the company total on Pune’s rows, the filter has to move into a later layer, or the total has to come from a subquery.

Several windows at once

You can mix windows and plain columns freely, and use different partitions in the same SELECT:

SELECT order_id, city, amount,
       sum(amount) OVER (PARTITION BY city)  AS city_total,
       sum(amount) OVER ()                    AS grand_total,
       count(*)    OVER (PARTITION BY customer_id) AS customer_orders
FROM orders;

When one window is reused many times, name it once with a WINDOW clause: WINDOW w AS (PARTITION BY city ORDER BY placed_at) at the end of the query, then sum(amount) OVER w. It keeps a ten-column query readable.

Where it works, and what it costs

Window functions are standard SQL and present in Postgres, DuckDB, BigQuery, SQL Server, SQLite from version 3.25 and MySQL from version 8. MySQL 5.7 does not have them, which is why older tutorials do the self-join contortions the earlier lesson showed. In spreadsheets the equivalent is a helper column with a formula that references other rows, which works until the sheet is sorted.

The cost is a sort. To compute a window with PARTITION BY city ORDER BY placed_at, the engine sorts the rows by city and date, then walks them. On a million rows that is fast; on a billion it is the thing to look at in the plan. Two windows with different orderings mean two sorts. Where you can, give every window in a query the same partition and order, so the engine sorts once.

The habit

When you catch yourself writing a CTE that aggregates and a join that brings the aggregate back to the rows, stop and ask whether a window does it in one line. Usually it does, it is easier to read, and it is one fewer place for the grain to go wrong.