What is a Window Function?

How it works

The OVER clause defines the window for each row:

• PARTITION BY splits rows into groups (like GROUP BY, but rows are not collapsed).
• ORDER BY orders rows inside each partition, which ranking and running calculations need.
• A frame such as ROWS BETWEEN 6 PRECEDING AND CURRENT ROW limits which rows an aggregate sees. With ORDER BY and no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats rows with equal sort values as peers.

The main families:

Family	Functions	Typical use
Ranking	ROW_NUMBER, RANK, DENSE_RANK, NTILE	Latest row per key, top N per group, deduplication
Offset	LAG, LEAD, FIRST_VALUE, LAST_VALUE	Change since the previous row, sessions, gaps
Aggregate	SUM, AVG, COUNT, MIN, MAX with OVER	Running totals, moving averages, share of total

Window functions run after WHERE, GROUP BY and HAVING, so you cannot filter on them in the same query’s WHERE; wrap the query in a CTE or subquery, or use QUALIFY where supported (Snowflake, Databricks, BigQuery). In Spark, a window with no PARTITION BY moves all rows into one partition, which does not scale.

Example

Order number, running total, change from the previous order and each order’s share of the customer’s total, all without losing rows (PostgreSQL 16).

CREATE TABLE orders (customer_id text, order_date date, amount int);
INSERT INTO orders VALUES
  ('C1', '2026-10-01', 40), ('C1', '2026-10-05', 25), ('C1', '2026-10-09', 60),
  ('C2', '2026-10-02', 10), ('C2', '2026-10-08', 30);

SELECT customer_id, order_date, amount,
       ROW_NUMBER() OVER w                        AS order_no,
       SUM(amount)  OVER w                        AS running_total,
       amount - LAG(amount) OVER w                AS change_vs_prev,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY customer_id)) AS pct_of_customer
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date)
ORDER BY customer_id, order_date;

 customer_id | order_date | amount | order_no | running_total | change_vs_prev | pct_of_customer
-------------+------------+--------+----------+---------------+----------------+-----------------
 C1          | 2026-10-01 |     40 |        1 |            40 |                |              32
 C1          | 2026-10-05 |     25 |        2 |            65 |            -15 |              20
 C1          | 2026-10-09 |     60 |        3 |           125 |             35 |              48
 C2          | 2026-10-02 |     10 |        1 |            10 |                |              25
 C2          | 2026-10-08 |     30 |        2 |            40 |             20 |              75

Common mistakes

• Using ROW_NUMBER for “top N” when ties should share a place (RANK or DENSE_RANK).
• Relying on the default RANGE frame for running totals when the order column has duplicates; use ROWS for row-by-row totals.
• Using LAST_VALUE with the default frame, which returns the current row, not the last in the partition.
• Filtering on a window function in WHERE instead of an outer query or QUALIFY.