{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"what-a-window-is","title":"A window: GROUP BY that keeps the rows","url":"https://addaly.com/learn/data-and-sql/what-a-window-is","position":34,"of":66,"minutes":9,"answer":"A window function computes an aggregate over a row's partition and writes it beside the row instead of collapsing the rows.","keyIdea":"A window function computes an aggregate over a row's partition and writes it beside the row instead of collapsing the rows, and because it runs after WHERE you filter on its result in a second layer."},"body":{"format":"markdown","text":"## The question GROUP BY cannot answer\n\n\"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.\n\n```sql\nSELECT order_id, city, amount,\n       sum(amount) OVER (PARTITION BY city)             AS city_total,\n       amount / sum(amount) OVER (PARTITION BY city)    AS share_of_city\nFROM orders;\n```\n\nEvery 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.\n\n`OVER ()` with nothing inside means the window is the whole result. `amount / sum(amount) OVER ()` is each order's share of everything.\n\n## Three parts, in order\n\nA window has up to three parts, and the later lessons in this module are each about one of them.\n\n- **PARTITION BY** decides which rows a row can see. Like GROUP BY, without the collapsing.\n- **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.\n- **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.\n\n## When it runs, and why that matters\n\nA 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:\n\n```sql\nSELECT *, row_number() OVER (ORDER BY amount DESC) AS rn\nFROM orders\nWHERE rn <= 10;\n-- ERROR: column \"rn\" does not exist\n```\n\n`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:\n\n```sql\nWITH ranked AS (\n  SELECT *, row_number() OVER (ORDER BY amount DESC) AS rn FROM orders\n)\nSELECT * FROM ranked WHERE rn <= 10;\n```\n\nThe 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.\n\n## Several windows at once\n\nYou can mix windows and plain columns freely, and use different partitions in the same SELECT:\n\n> Figure: The three parts of a window, and what each one decides. Layers, top to bottom: PARTITION BY (which rows this row is allowed to see); ORDER BY, inside the OVER (puts those rows in a sequence); The frame (how much of that sequence the aggregate covers); Computed after WHERE, GROUP BY and HAVING (so filter its result in a second layer); Computed before ORDER BY and LIMIT (the window sees the rows WHERE already kept). PARTITION BY is GROUP BY without the collapsing. The ORDER BY inside OVER is what makes \"previous row\" and \"running total\" mean anything. The frame is the part with a default that surprises people, and it exists only once there is an ORDER BY.\n\n```sql\nSELECT order_id, city, amount,\n       sum(amount) OVER (PARTITION BY city)  AS city_total,\n       sum(amount) OVER ()                    AS grand_total,\n       count(*)    OVER (PARTITION BY customer_id) AS customer_orders\nFROM orders;\n```\n\nWhen 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.\n\n## Where it works, and what it costs\n\nWindow 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.\n\nThe 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.\n\n## The habit\n\nWhen 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."},"check":{"question":"A query computes `count(*) OVER () AS total_rows` and filters `WHERE city = 'Pune'`. The analyst expected `total_rows` to be the size of the whole table but gets Pune's row count on every row. Why?","options":[{"text":"The window function runs after WHERE, so its partition contains only the rows that survived the filter.","correct":true},{"text":"`OVER ()` with an empty partition defaults to partitioning by the first column in the WHERE clause, which here is city.","correct":false,"tempting":"An empty OVER means one partition holding every visible row; there is no implicit partition drawn from WHERE."},{"text":"`count(*)` inside a window counts distinct values of the ordering column rather than rows, and Pune has that many distinct dates.","correct":false,"tempting":"There is no ordering column in this window, and count(*) counts rows in every context."},{"text":"The database rewrote the window into a GROUP BY on city because the WHERE clause referenced that column, collapsing the count to the group.","correct":false,"tempting":"Windows are never rewritten into GROUP BY; the rows remain and the count is simply over the filtered set."}]},"prev":{"slug":"reconciling-a-total","title":"Reconciling: making a total tie out before you send it","url":"https://addaly.com/learn/data-and-sql/reconciling-a-total"},"next":{"slug":"ranking-functions","title":"ROW_NUMBER, RANK and DENSE_RANK: three answers to \"what position\"","url":"https://addaly.com/learn/data-and-sql/ranking-functions"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'A window: GROUP BY that keeps the rows', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/what-a-window-is (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"A window: GROUP BY that keeps the rows","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":4,"slug":"windows-sequences-and-time","title":"Windows, sequences and time"},"answer":null,"keyIdea":"A window function computes an aggregate over a row’s partition and writes it beside the row instead of collapsing the rows, and because it runs after WHERE you filter on its result in a second layer.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure 4.1","figure":{"type":"stack","title":"The three parts of a window, and what each one decides","layers":[{"label":"PARTITION BY","detail":"which rows this row is allowed to see"},{"label":"ORDER BY, inside the OVER","detail":"puts those rows in a sequence"},{"label":"The frame","detail":"how much of that sequence the aggregate covers"},{"label":"Computed after WHERE, GROUP BY and HAVING","detail":"so filter its result in a second layer"},{"label":"Computed before ORDER BY and LIMIT","detail":"the window sees the rows WHERE already kept"}],"caption":"PARTITION BY is GROUP BY without the collapsing. The ORDER BY inside OVER is what makes “previous row” and “running total” mean anything. The frame is the part with a default that surprises people, and it exists only once there is an ORDER BY."},"alt":"Figure: The three parts of a window, and what each one decides. Layers, top to bottom: PARTITION BY (which rows this row is allowed to see); ORDER BY, inside the OVER (puts those rows in a sequence); The frame (how much of that sequence the aggregate covers); Computed after WHERE, GROUP BY and HAVING (so filter its result in a second layer); Computed before ORDER BY and LIMIT (the window sees the rows WHERE already kept). PARTITION BY is GROUP BY without the collapsing. The ORDER BY inside OVER is what makes “previous row” and “running total” mean anything. The frame is the part with a default that surprises people, and it exists only once there is an ORDER BY."}},"blocks":[{"t":"h2","id":"the-question-group-by-cannot-answer","runs":[{"text":"The question GROUP BY cannot answer"}]},{"t":"p","runs":[{"text":"“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."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT order_id, city, amount,\n       sum(amount) OVER (PARTITION BY city)             AS city_total,\n       amount / sum(amount) OVER (PARTITION BY city)    AS share_of_city\nFROM orders;"},{"t":"p","runs":[{"text":"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. "},{"text":"OVER","code":true},{"text":" is the word that turns an aggregate into a window function. "},{"text":"PARTITION BY city","code":true},{"text":" says which rows count as “the others”: the window a row looks through is its own partition."}]},{"t":"p","runs":[{"text":"OVER ()","code":true},{"text":" with nothing inside means the window is the whole result. "},{"text":"amount / sum(amount) OVER ()","code":true},{"text":" is each order’s share of everything."}]},{"t":"h2","id":"three-parts-in-order","runs":[{"text":"Three parts, in order"}]},{"t":"p","runs":[{"text":"A window has up to three parts, and the later lessons in this module are each about one of them."}]},{"t":"ul","items":[[{"text":"PARTITION BY","b":true},{"text":" decides which rows a row can see. Like GROUP BY, without the collapsing."}],[{"text":"ORDER BY","b":true},{"text":", 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."}],[{"text":"The frame","b":true},{"text":" 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."}]]},{"t":"h2","id":"when-it-runs-and-why-that-matters","runs":[{"text":"When it runs, and why that matters"}]},{"t":"p","runs":[{"text":"A window function is computed after "},{"text":"WHERE","code":true},{"text":", "},{"text":"GROUP BY","code":true},{"text":" and "},{"text":"HAVING","code":true},{"text":" have finished and before "},{"text":"ORDER BY","code":true},{"text":" and "},{"text":"LIMIT","code":true},{"text":". That order explains the first error everybody hits:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT *, row_number() OVER (ORDER BY amount DESC) AS rn\nFROM orders\nWHERE rn <= 10;\n-- ERROR: column \"rn\" does not exist"},{"t":"p","runs":[{"text":"WHERE","code":true},{"text":" 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:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH ranked AS (\n  SELECT *, row_number() OVER (ORDER BY amount DESC) AS rn FROM orders\n)\nSELECT * FROM ranked WHERE rn <= 10;"},{"t":"p","runs":[{"text":"The same order explains a quieter fact: a window function sees the rows after "},{"text":"WHERE","code":true},{"text":" has applied. "},{"text":"sum(amount) OVER ()","code":true},{"text":" in a query with "},{"text":"WHERE city = 'Pune'","code":true},{"text":" 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."}]},{"t":"h2","id":"several-windows-at-once","runs":[{"text":"Several windows at once"}]},{"t":"p","runs":[{"text":"You can mix windows and plain columns freely, and use different partitions in the same SELECT:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT order_id, city, amount,\n       sum(amount) OVER (PARTITION BY city)  AS city_total,\n       sum(amount) OVER ()                    AS grand_total,\n       count(*)    OVER (PARTITION BY customer_id) AS customer_orders\nFROM orders;"},{"t":"p","runs":[{"text":"When one window is reused many times, name it once with a "},{"text":"WINDOW","code":true},{"text":" clause: "},{"text":"WINDOW w AS (PARTITION BY city ORDER BY placed_at)","code":true},{"text":" at the end of the query, then "},{"text":"sum(amount) OVER w","code":true},{"text":". It keeps a ten-column query readable."}]},{"t":"h2","id":"where-it-works-and-what-it-costs","runs":[{"text":"Where it works, and what it costs"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"The cost is a sort. To compute a window with "},{"text":"PARTITION BY city ORDER BY placed_at","code":true},{"text":", 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."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"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."}]}],"check":{"question":[{"text":"A query computes "},{"text":"count(*) OVER () AS total_rows","code":true},{"text":" and filters "},{"text":"WHERE city = 'Pune'","code":true},{"text":". The analyst expected "},{"text":"total_rows","code":true},{"text":" to be the size of the whole table but gets Pune’s row count on every row. Why?"}],"options":[{"text":[{"text":"The window function runs after WHERE, so its partition contains only the rows that survived the filter."}],"correct":true,"tempting":null},{"text":[{"text":"OVER ()","code":true},{"text":" with an empty partition defaults to partitioning by the first column in the WHERE clause, which here is city."}],"correct":false,"tempting":[{"text":"An empty OVER means one partition holding every visible row; there is no implicit partition drawn from WHERE."}]},{"text":[{"text":"count(*)","code":true},{"text":" inside a window counts distinct values of the ordering column rather than rows, and Pune has that many distinct dates."}],"correct":false,"tempting":[{"text":"There is no ordering column in this window, and count(*) counts rows in every context."}]},{"text":[{"text":"The database rewrote the window into a GROUP BY on city because the WHERE clause referenced that column, collapsing the count to the group."}],"correct":false,"tempting":[{"text":"Windows are never rewritten into GROUP BY; the rows remain and the count is simply over the filtered set."}]}],"order":[1,3,2,0],"why":null},"prev":{"slug":"reconciling-a-total","title":"Reconciling: making a total tie out before you send it","minutes":8},"next":{"slug":"ranking-functions","title":"ROW_NUMBER, RANK and DENSE_RANK: three answers to “what position”","minutes":8}}}