Window Function Basics

The Mental Model: Windows Over Rows

A window function computes a value across a set of rows related to the current row, without collapsing those rows into a single output row. This is the critical distinction from GROUP BY: aggregation collapses many rows into one; a window function keeps every row and adds a computed column alongside.

Consider a simple aggregate query:

SELECT department, AVG(salary) AS dept_avg
FROM employees
GROUP BY department;

-- Result: one row per department
-- Engineering | 88500
-- Marketing   | 71500
-- HR          | 68000

The output has three rows. The individual employees are gone — collapsed into their department averages. Now consider the same data with a window function:

SELECT
    first_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

-- Result: one row per employee, with the department average repeated
-- Alice | Engineering | 92000 | 88500
-- Bob   | Engineering | 85000 | 88500
-- Carol | Marketing   | 72000 | 71500
-- Dave  | Marketing   | 71000 | 71500

Every employee row is preserved. The AVG(salary) OVER (PARTITION BY department) computes the average within each department partition and attaches it to every row in that partition. You get the aggregate context without losing the detail.

The OVER clause is what makes a function a window function. Without OVER, AVG(salary) is a plain aggregate that collapses rows. With OVER, it becomes a window function that preserves rows. The OVER clause defines the window — the set of rows the function operates on for each output row.

The three components of an OVER clause are:

• PARTITION BY: divides the result set into groups (like GROUP BY, but without collapsing). Rows in different partitions never see each other.
• ORDER BY: defines the logical ordering within each partition. Required for ranking and offset functions; optional for plain aggregates.
• Frame clause (ROWS BETWEEN ... AND ...): further restricts which rows within the partition are included in the computation for the current row.

AVG(salary) OVER (PARTITION BY department ORDER BY hire_date)

This computes a running average within each department, ordered by hire date. The frame clause is covered in detail in the chapter on running totals.

PARTITION BY: Grouping Without Collapsing

PARTITION BY is the most important component of the OVER clause. It tells the database how to divide the result set into independent groups, each of which is processed separately by the window function. The key difference from GROUP BY is that partitioning does not reduce the number of rows — it only scopes the computation.

A common analytical pattern is comparing each row to its group's aggregate. For example, how does each employee's salary compare to their department's average and maximum?

SELECT
    first_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    MAX(salary) OVER (PARTITION BY department) AS dept_max,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

Each window function operates independently within its partition. The AVG and MAX are computed per department, and the diff_from_avg subtracts the department average from each individual salary. All of this happens in a single pass over the data — no self-joins, no subqueries.

Multiple partitions. You can partition by more than one column. The combination of column values defines the partition:

SELECT
    first_name,
    department,
    location,
    salary,
    AVG(salary) OVER (PARTITION BY department, location) AS dept_location_avg
FROM employees;

This computes the average salary for each (department, location) pair. An employee in Engineering at NYC is compared only to other Engineering-NYC employees, not to Engineering employees in other locations.

Omitting PARTITION BY. If you leave out PARTITION BY, the entire result set is treated as a single partition. This is useful for global computations:

SELECT
    first_name,
    salary,
    AVG(salary) OVER () AS company_avg,
    salary - AVG(salary) OVER () AS diff_from_company_avg
FROM employees;

The empty OVER () means "no partitioning — compute over all rows." Every row gets the same company-wide average attached.

Interaction with GROUP BY. Window functions are evaluated after GROUP BY and HAVING. This means you can aggregate first, then apply window functions to the aggregated result:

SELECT
    department,
    COUNT(*) AS headcount,
    AVG(salary) AS dept_avg,
    RANK() OVER (ORDER BY AVG(salary) DESC) AS salary_rank
FROM employees
GROUP BY department;

The GROUP BY collapses to one row per department. The window function then ranks those department rows by their average salary. This two-level pattern — aggregate, then window over the aggregate — is extremely common in reporting queries.

Aggregate Window Functions

Every aggregate function you already know — SUM, AVG, COUNT, MIN, MAX — can be used as a window function by adding an OVER clause. The function operates over the window frame instead of collapsing the entire group.

SELECT
    order_id,
    customer_id,
    order_date,
    total,
    SUM(total) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
    AVG(total) OVER (PARTITION BY customer_id) AS customer_avg_order,
    COUNT(*) OVER (PARTITION BY customer_id) AS customer_order_count
FROM orders;

Three different window functions, each with a different OVER clause:

• running_total: partitioned by customer, ordered by date — a cumulative sum that grows with each order.
• customer_avg_order: partitioned by customer, no ordering — the same average attached to every order row.
• customer_order_count: partitioned by customer, no ordering — the total number of orders for that customer, repeated on each row.

The ORDER BY in the first function changes its behavior fundamentally. Without ORDER BY, SUM computes the total over the entire partition. With ORDER BY, SUM computes a running total from the start of the partition up to the current row. This is the default frame behavior when ORDER BY is present: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

COUNT as a window function. COUNT is particularly useful for per-group numbering and distribution analysis:

SELECT
    product_name,
    category,
    price,
    COUNT(*) OVER (PARTITION BY category) AS products_in_category,
    COUNT(*) OVER (PARTITION BY category, price) AS products_at_same_price
FROM products;

The first COUNT tells you how many products share each category. The second tells you how many products share the exact same price within that category. Both are computed in a single pass.

Window functions in WHERE. A common mistake is trying to use a window function in a WHERE clause:

-- ERROR: window functions are not allowed in WHERE
SELECT * FROM employees
WHERE AVG(salary) OVER (PARTITION BY department) > 80000;

Window functions are evaluated after WHERE, so they cannot be filtered there. The workaround is to wrap the query in a CTE or subquery and filter in the outer query:

WITH dept_context AS (
    SELECT
        first_name,
        department,
        salary,
        AVG(salary) OVER (PARTITION BY department) AS dept_avg
    FROM employees
)
SELECT * FROM dept_context WHERE dept_avg > 80000;

This pattern — compute window functions in a CTE, then filter — is the standard way to build multi-layered analytical queries.

Normalization and Percentage of Total

Window functions excel at normalization — expressing each value relative to its group. The two most common forms are percentage of group total and z-score standardization.

Percentage of total. For each row, compute what fraction of its partition's total it represents:

SELECT
    product_name,
    category,
    revenue,
    SUM(revenue) OVER (PARTITION BY category) AS category_revenue,
    ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY category), 2) AS pct_of_category
FROM product_sales;

The window function SUM(revenue) OVER (PARTITION BY category) computes the category total once per partition. Dividing each row's revenue by that total gives its share. Multiplying by 100 converts to a percentage. This pattern appears in virtually every business dashboard: sales by region as a percentage of total, marketing spend by channel as a percentage of budget, support tickets by product as a percentage of all tickets.

Z-score standardization. To compare values across groups with different scales, convert each value to a z-score: how many standard deviations it is from its group mean.

SELECT
    employee_name,
    department,
    salary,
    ROUND(
        (salary - AVG(salary) OVER (PARTITION BY department))
        / NULLIF(STDDEV(salary) OVER (PARTITION BY department), 0),
    2) AS z_score
FROM employees;

The numerator is the deviation from the mean. The denominator is the standard deviation within the same partition. The NULLIF guards against division by zero when a department has only one employee (standard deviation is zero or NULL). A z-score of 1.5 means the employee earns 1.5 standard deviations above their department average — a meaningful comparison even if the departments have very different salary ranges.

Row-level share within ordered partitions. When you need each row's position within its partition as a fraction:

SELECT
    student_name,
    class,
    score,
    ROUND(
        CAST(ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS FLOAT)
        / COUNT(*) OVER (PARTITION BY class),
    2) AS percentile_rank
FROM exam_scores;

This computes a simple percentile: the student's rank divided by the class size. A value of 0.1 means the student is in the top 10% of their class. This is a simplified percentile — for precise percentiles, use the PERCENT_RANK() or CUME_DIST() window functions, which handle ties and edge cases more rigorously.

The unifying principle: window functions let you express relative measures (share, rank, deviation) in a single query without self-joins or correlated subqueries. The computation is declarative — you describe what you want, and the database figures out how to compute it efficiently.