Window functions (OVER)

Compute a value over a set of related rows while keeping every row in the result.

Syntax

function(...) OVER (
  [PARTITION BY expr, ...]
  [ORDER BY expr [ASC|DESC], ...]
  [frame_clause]
)
... WINDOW w AS (PARTITION BY ... ORDER BY ...)   -- named window, used as OVER w

A window function looks at a "window" of rows related to the current one, but unlike GROUP BY it does not collapse rows: every input row stays, with an extra computed column.

PARTITION BY splits rows into independent groups (like GROUP BY without collapsing). Omit it to use all rows.
ORDER BY inside OVER defines order within the partition: needed for ranking, LAG/LEAD and running totals.
Any aggregate works as a window function: sum(x) OVER (PARTITION BY g) is the group total on every row.
Default frame: with ORDER BY in OVER, aggregates cover the partition start through the current row and its peers (ties) — a running total. Without ORDER BY, they cover the whole partition.
Window functions run after WHERE/GROUP BY/HAVING, so they cannot appear in those clauses. Filter on them in an outer query (subquery or CTE).
You can window over aggregates: sum(sum(x)) OVER () = grand total of the group totals.

Examples

Group total and share on every row starter

SELECT product_name, category, price,
       sum(price) OVER (PARTITION BY category) AS category_total,
       round(100 * price / sum(price) OVER (PARTITION BY category), 1) AS pct_of_category
FROM products
ORDER BY category, price DESC;

Every product row stays; the category total repeats on each row of its category.

Running total starter

SELECT order_date, order_id, quantity,
       sum(quantity) OVER (ORDER BY order_date, order_id) AS running_units
FROM orders
ORDER BY order_date, order_id;

With ORDER BY in OVER, sum accumulates from the first row to the current one.

Window over an aggregate shop

SELECT channel, count(*) AS orders,
       round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS pct_of_orders
FROM orders
GROUP BY channel
ORDER BY orders DESC;

GROUP BY runs first; the window then sums the grouped counts across all rows.

Pitfalls

Using a window function in WHERE (window functions are not allowed in WHERE).
Expecting a running total to be per row when the ORDER BY key has ties: the default RANGE frame includes all peers. Add a unique tie-breaker or use ROWS.
Using last_value() with the default frame (it returns the current row's value).

Other databases

For orientation only: SQL Quest executes PostgreSQL.

MySQL: Window functions since 8.0.
BigQuery: QUALIFY filters on window results directly: QUALIFY row_number() OVER (...) = 1.
Snowflake: Supports QUALIFY.
SQLite: Window functions since 3.25.