Why use a window function instead of joining back to a GROUP BY subquery for per-row aggregates?

## The problem both forms solve You want each detail row plus an aggregate of the group it belongs to: every order line with its customer's lifetime total, every employee with their department's average salary, every event with its session's event count. ## Form 1 — aggregate, then join back ```sql SELECT o.order_id, o.customer_id, o.amount, t.customer_total FROM orders o JOIN (SELECT customer_id, SUM(amount) AS customer_total FROM orders GROUP BY customer_id) t ON t.customer_id = o.customer_id; ``` The derived table collapses to one row per customer; the join re-expands it back onto the detail rows. This is correct, portable to any SQL engine ever shipped, and was the only option before window functions were widely available. ## Form 2 — window function ```sql SELECT order_id, customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id) AS customer_total FROM orders; ``` One row source, one `SELECT`, same result columns. ## What the window form buys you **The predicate lives in one place.** This is the argument that actually matters. Add `WHERE status = 'PAID'` to the join-back version and you must add it *inside the derived table too*. Put it only on the outer query and you get paid orders displayed next to totals that silently include unpaid ones — a wrong number that looks entirely plausible on a dashboard. In the window form there is one `WHERE`; the window is computed over exactly the rows that survived it, so the total always describes the rows on screen. **Fewer moving parts to get wrong.** No group key repeated in the `GROUP BY`, the `ON` clause and the select list; no risk of joining on the wrong column or an incomplete key. If the group key is a composite of three columns, the window form writes them once in `PARTITION BY`. **No accidental change in row count.** A join back on the true group key of an aggregated set is one-to-one per detail row, so it does not duplicate — but only if the join key really is the aggregate's grouping key. Get that wrong (join on a key the subquery did not group by) and detail rows multiply. The window form has no join, so this failure mode does not exist. **NULL group keys survive.** If `customer_id` can be `NULL`, the inner join drops those detail rows entirely, because `NULL = NULL` is not true. The window form keeps them and treats all `NULL` keys as one partition. Whether that is what you want is a design decision, but it should be a decision, not an accident. **It composes.** You can add a rank, a running total, a lag and a partition total in the same `SELECT` list, each with its own `OVER` clause. The join-back form needs one derived table per shape. ## When the join-back form is still right - **A different table or grain.** If the aggregate comes from another table — item counts from `order_items` attached to `orders` — you cannot partition `orders` to get it. Aggregate `order_items` in a derived table or CTE and join. (A correlated subquery or a lateral join are the other two shapes for that job.) - **A denominator that must ignore this query's filter.** Because the window sees only post-`WHERE` rows, a company-wide total inside a region-filtered query cannot come from a plain `SUM() OVER ()` in that same query. - **An engine without window functions.** MySQL added window functions in 8.0 and SQLite in 3.25; older targets need the join-back form. ## Readability, honestly assessed The window form is shorter but assumes the reader knows what `OVER` means. On a team where that is true — which today is most teams — it is the clearer statement of intent: "show me each row and its group's total" reads directly off the syntax, whereas the join-back version makes the reader reconstruct the intent from a collapse followed by a re-expansion. ## What an interviewer is checking That you can write both, that you name the duplicated-predicate trap rather than only saying "windows are cleaner", and that you know the boundary: same-table group aggregate → window; other table or other grain → aggregate and join.