Window Functions vs GROUP BY

## The one-line difference `GROUP BY` **collapses**. A window function **annotates**. Both can compute `SUM(sales)` per region; only the row count of the result differs. ## What GROUP BY does When a query has `GROUP BY region`, the engine partitions the rows that survived `WHERE` into groups sharing the same `region` value, and then produces **exactly one output row per group**. Everything the SELECT list asks for must be derivable from the group: the grouping column itself, or an aggregate over the group's rows. The individual rows are gone from the output — there is no place to put `rep` or `sale_id`, because a group of five rows has five different values for them and only one output row to hold them. ```sql SELECT region, SUM(amount) AS region_total FROM sales GROUP BY region; -- EAST | 300 -- WEST | 250 ``` ## What a window function does A window function is written with an `OVER` clause. It runs over the same set of rows, but it does not reduce them. For each row, the engine determines that row's **window** — here, all rows sharing its `region`, because of `PARTITION BY region` — computes the aggregate over that window, and returns the value **as an extra column on that row**. Every input row still comes out. ```sql SELECT region, rep, amount, SUM(amount) OVER (PARTITION BY region) AS region_total FROM sales; -- EAST | ana | 100 | 300 -- EAST | bo | 120 | 300 -- EAST | cy | 80 | 300 -- WEST | di | 150 | 250 -- WEST | ed | 100 | 250 ``` Notice `region_total` repeats across the rows of a partition. That repetition is the point: it lets the same row show both its own value and its group's value, so you can compute a share, a difference from the group average, or a flag like "above the regional mean" without a second query. ## PARTITION BY is not WHERE and not GROUP BY Three confusions are worth naming explicitly. - **`PARTITION BY` does not filter.** It slices the surviving rows into buckets for the window computation. Rows in other partitions are excluded from *this row's* window, not from the result set. - **`PARTITION BY` does not require a matching `GROUP BY`.** The two clauses are independent; a query can have a window function and no `GROUP BY` at all, which is the common case. - **A window function does not deduplicate.** If you want one row per region you need `GROUP BY` (or a deduplicating step around the window query, which is strictly more work for the same answer). ## Choosing between them Ask what the consumer of the result wants: - A report with one line per group, an export feeding a summary table, a set to join against at group grain → `GROUP BY`. - A detail listing where each line also shows group context — total, average, rank, share → a window function. - Both at once → they compose: a grouped query can itself carry window functions computed over the grouped rows. There is also a readability argument. Before window functions were widely available, getting detail plus group aggregate required aggregating in a subquery and joining the result back to the detail rows on the group key. That works, but it repeats the group key, duplicates any `WHERE` predicate in two places, and gives you two places to get the filter wrong. The window form expresses the same thing in one `SELECT`. ## Rules the window form still obeys A window function sees only the rows that reached it — the rows left after `WHERE` (and after grouping, in a grouped query). It does not reach back to rows the query filtered out. So `SUM(amount) OVER (PARTITION BY region)` in a query with `WHERE sale_date >= DATE '2024-01-01'` totals **this year's** sales for the region, not all of history. That is usually what you want, but it is a fact to state out loud rather than assume. ## What an interviewer is checking That you can predict the row count of each form, that you know `PARTITION BY` is a slicing device rather than a filter, and that you pick the form matching the required output shape instead of reaching for whichever one you learned first.