Windowed Aggregates — Use OVER(PARTITION BY ...) for windowed aggregates - SQL

SECTION 1

Historical Context & Motivation

For the first two decades of SQL's life, computing an aggregate alongside the detail rows that produced it required either a self-join, a correlated subquery, or post-processing in the application layer. A developer who wanted each employee's salary next to the department average had to write a GROUP BY subquery, join it back to the base table, and hope the optimizer could merge the two scans. The verbosity was painful, the performance often worse. The relational model, as formalized by E. F. Codd in 1970, provided powerful set-based operations, yet it offered no native way to annotate individual rows with summary statistics computed across a sliding or partitioned window of related rows.

The need grew as analytical workloads — ranking, running totals, moving averages, and percentile calculations — migrated from spreadsheets and OLAP cubes into the database itself. Database vendors began experimenting with proprietary syntax in the late 1990s, and the ISO standards committee responded by formalizing window functions in SQL:2003. The OVER(PARTITION BY ...) clause became the syntactic gateway to a fundamentally new class of operations: aggregates that do not collapse rows.

1970 • Codd's Relational Model

E. F. Codd publishes "A Relational Model of Data for Large Shared Data Banks," establishing set-based query semantics. Aggregation is strictly row-collapsing via GROUP BY.

1992 • SQL-92 Standard

The ANSI/ISO SQL-92 standard codifies GROUP BY and HAVING but provides no mechanism for per-row aggregate annotations. Complex analytical queries require subqueries or temp tables.

1999 • Oracle 8i Analytic Functions

Oracle introduces proprietary analytic functions with an OVER clause, demonstrating that windowed computation can be expressed declaratively and optimized by the engine.

2003 • SQL:2003 Window Functions

The ISO SQL:2003 standard formally specifies window functions, including PARTITION BY, ORDER BY, and frame clauses. All major RDBMS vendors begin adoption.

2012–Present • Universal Adoption

PostgreSQL, MySQL 8.0, SQL Server, SQLite 3.25, and cloud warehouses (BigQuery, Snowflake, Redshift) all support window functions, making them a de facto standard tool for analytics.

The central question that windowed aggregates answer is deceptively simple: how can we compute a summary value over a group of rows and attach that value to every row in the group, all within a single query pass? Understanding the answer unlocks an entire family of analytical patterns — from running totals to moving averages to ratio-to-total calculations — that would otherwise require procedural code or multiple query layers.

SECTION 2

Core Principles & Definitions

A windowed aggregate applies a standard aggregate function — SUM, AVG, COUNT, MIN, MAX, and others — to a window of rows defined by the OVER clause. Unlike a traditional GROUP BY aggregate that merges rows into a single output row per group, a windowed aggregate preserves every input row and simply appends the computed value as an additional column. This non-destructive behavior is the defining characteristic of window functions and the reason they are sometimes called analytic functions.

Window Function Syntax

Every windowed aggregate takes the form AGG_FUNC(expr) OVER (PARTITION BY col ORDER BY col ROWS/RANGE frame). The OVER clause is mandatory and distinguishes it from a regular aggregate.

PARTITION BY

Divides the result set into non-overlapping partitions (analogous to groups). The aggregate is computed independently within each partition. Omitting PARTITION BY treats the entire result set as one partition.

Row Preservation

Unlike GROUP BY, no rows are collapsed. Each row retains its identity and original columns; the windowed aggregate merely adds a new derived column alongside the existing data.

Evaluation Order

Window functions are evaluated after WHERE, GROUP BY, and HAVING. They operate on the result set produced by those clauses, which is why they cannot appear in a WHERE clause directly.

Frame Clause (Optional)

Within each partition, a frame specification (ROWS BETWEEN or RANGE BETWEEN) can restrict the aggregate to a sliding subset of rows. Without ORDER BY, the default frame is all rows in the partition.

✦

KEY TAKEAWAY

Think of GROUP BY as a paper shredder: it reduces every group of rows down to a single summary line. OVER(PARTITION BY ...) is more like a transparent overlay — it computes the same summary but writes the answer in the margin of every original row. The underlying data passes through untouched, augmented rather than replaced.

SECTION 3

Visual Explanation

The following diagram contrasts the data flow of a GROUP BY aggregate with a windowed aggregate using OVER(PARTITION BY dept). On the left, the traditional aggregate collapses four input rows into two summary rows. On the right, the windowed aggregate retains all four rows and appends a new column containing the partition-level sum.

Left: GROUP BY dept collapses four rows into two summary rows. Right: SUM(salary) OVER(PARTITION BY dept) preserves all four original rows and appends a dept_total column. Cyan rows belong to the Eng partition; violet rows to Sales.

Notice how the right-hand output is strictly a superset of the original data. Every detail column — emp, dept, salary — survives, and the engine simply broadcasts the partition-level sum to each member row. This property makes windowed aggregates composable: you can add multiple OVER expressions in a single SELECT, each with a different PARTITION BY key, without ever losing row-level detail or performing additional joins.

SECTION 4

How the Engine Evaluates Windowed Aggregates

Understanding the logical evaluation order of a SQL query is essential to using window functions correctly. The SQL standard specifies that a query is processed in a series of conceptual phases, and window functions occupy a very specific slot in that pipeline. Misunderstanding this order is the most common source of confusion for developers new to OVER clauses.

Logical Query Processing Order

SQL LOGICAL EVALUATION ORDER

FROM → WHERE → GROUP BY → HAVING → SELECT (window functions) → DISTINCT → ORDER BY → LIMIT

Window functions are evaluated during the SELECT phase, after all filtering and grouping. This means they cannot appear in WHERE or HAVING. To filter on a window function result, wrap the query in a CTE or subquery.

The Three Components of OVER

GENERAL SYNTAX

aggregate_function(expression) OVER ( [PARTITION BY p₁, p₂, ...] [ORDER BY o₁, o₂, ...] [frame_clause] )

PARTITION BY — splits rows into independent partitions (like GROUP BY but non-collapsing). ORDER BY — defines a logical ordering within each partition; required for running/cumulative aggregates. frame_clause — restricts the aggregate to a sliding subset (e.g., ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for a 3-row moving average).

⚠

Default Frame Behavior

When you write SUM(salary) OVER(PARTITION BY dept) without ORDER BY, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, meaning the aggregate spans all rows in the partition. If you add ORDER BY, the default frame shrinks to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, producing a running aggregate. This subtle distinction catches many developers off guard.

Internally, most query engines implement windowed aggregates in two passes. First, a sort-and-partition step orders rows by the PARTITION BY and ORDER BY keys. Second, a scan-and-accumulate step iterates through each partition, maintaining a running accumulator for the aggregate function. For partition-wide aggregates without ORDER BY, the engine can often compute the aggregate once per partition and broadcast the value to all member rows, resulting in performance comparable to a single GROUP BY scan plus a hash join back to the original rows — but without the syntactic overhead.

SECTION 5

Common Windowed Aggregate Patterns

Windowed aggregates unlock a rich set of analytical patterns. The table below catalogs the most frequently used patterns, each distinguished by its PARTITION BY, ORDER BY, and frame clause configuration. Mastering these patterns is largely what separates a competent SQL analyst from one who repeatedly falls back on procedural code.

Pattern	SQL Example	Behavior
Partition Total	`SUM(amt) OVER(PARTITION BY region)`	Computes the total for each region and broadcasts it to every row in that region.
Ratio to Total	`amt / SUM(amt) OVER(PARTITION BY region)`	Each row's value as a fraction of the partition total — a percentage breakdown without a subquery.
Running Sum	`SUM(amt) OVER(PARTITION BY region ORDER BY dt)`	Cumulative total within each region, ordered by date. The default frame ends at the current row.
Moving Average	`AVG(amt) OVER(ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)`	Three-row sliding average. Frame clause explicitly limits the window to the current row and two prior rows.
Partition Count	`COUNT(*) OVER(PARTITION BY dept)`	Number of rows per partition, appended to each row. Useful for filtering partitions by size in an outer query.
Grand Total	`SUM(amt) OVER()`	Empty OVER clause = the entire result set is one partition. Every row gets the grand total.

Common windowed aggregate patterns and their behavior

Running SUM partitioned by region and ordered by month. Each data point represents the cumulative revenue for that region up to and including that month. The East region accumulates faster, reaching $70k by June, while West reaches $40k. Each partition is computed independently.

The chart above illustrates how the ORDER BY clause transforms a partition-total pattern into a running-total pattern. Without ORDER BY, each month's point would show the same final total (70k for East, 40k for West). With ORDER BY month, the aggregate accumulates row by row within each partition, yielding the staircase-like growth curves above. This is the power of combining PARTITION BY with ORDER BY: it slices the data into groups and then imposes a computational direction within each group.

SECTION 6

Worked Example

Consider a table orders with columns order_id, customer_id, order_date, and amount. The task is to produce a report that shows every order alongside the customer's total spend, the customer's average order value, and each order's percentage of the customer's total spend — all in a single query.

Multi-Window Aggregate Report

1
Step 1 — Identify the Partition Key

Since we want aggregates scoped to each customer, the partition key is customer_id. Every window function in the query will include OVER(PARTITION BY customer_id).

PARTITION BY customer_id
2
Step 2 — Write the Windowed SUM for Total Spend

To get each customer's total spend replicated on every row, we write: SUM(amount) OVER(PARTITION BY customer_id) AS cust_total. No ORDER BY is needed because we want the full partition sum, not a running total.

SUM(amount) OVER(PARTITION BY customer_id) AS cust_total
3
Step 3 — Add the Windowed AVG

Similarly, the average order value per customer is: AVG(amount) OVER(PARTITION BY customer_id) AS cust_avg. Both window functions share the same partition definition, so the engine can compute them in a single sort-and-scan pass.

AVG(amount) OVER(PARTITION BY customer_id) AS cust_avg
4
Step 4 — Compute the Ratio to Total

The percentage each order contributes to the customer's total is simply the row's amount divided by the windowed SUM. We can use ROUND(100.0 * amount / SUM(amount) OVER(PARTITION BY customer_id), 2) AS pct_of_total. Multiplying by 100.0 (a decimal literal) ensures floating-point division in databases that default to integer division.

ROUND(100.0 * amount / SUM(amount) OVER(PARTITION BY customer_id), 2) AS pct_of_total
5
Step 5 — Assemble the Full Query

Combining all expressions into a single SELECT yields: SELECT order_id, customer_id, order_date, amount, SUM(amount) OVER(PARTITION BY customer_id) AS cust_total, AVG(amount) OVER(PARTITION BY customer_id) AS cust_avg, ROUND(100.0 * amount / SUM(amount) OVER(PARTITION BY customer_id), 2) AS pct_of_total FROM orders ORDER BY customer_id, order_date; This query scans the orders table once, partitions by customer_id, computes SUM and AVG for each partition, and attaches the results to every row. The final ORDER BY is purely for presentation.

Complete query produces all detail rows plus three analytics columns — no subqueries, no joins.

order_id	customer_id	amount	cust_total	cust_avg	pct_of_total
1001	A	200	500	166.67	40.00
1002	A	150	500	166.67	30.00
1003	A	150	500	166.67	30.00
1004	B	300	700	350.00	42.86
1005	B	400	700	350.00	57.14

Sample output for the multi-window aggregate query

SECTION 7

Windowed Aggregates vs. GROUP BY: Strengths & Limitations

Windowed aggregates and GROUP BY aggregates are not interchangeable; they answer fundamentally different questions. GROUP BY produces a summary result set in which each output row represents an entire group, whereas a windowed aggregate produces a detail result set in which each output row represents a single original record, enriched with group-level metrics. Choosing the wrong tool leads to either information loss or unnecessary complexity.

Dimension	GROUP BY Aggregate	OVER(PARTITION BY ...) Aggregate
Output cardinality	One row per group (reduces row count)	Same row count as input (preserves every row)
Detail columns	Only grouped/aggregated columns allowed in SELECT	All original columns remain accessible
Running/cumulative	Not directly possible; requires self-join or correlated subquery	Native support via ORDER BY within the frame clause
Filtering on result	HAVING clause filters groups	Must wrap in CTE/subquery and apply WHERE on outer query
Performance	Typically a single hash aggregate; very efficient for pure summaries	Sort + scan; can be more expensive for large datasets without indexes on partition keys
Composability	Adding a second grouping level requires nesting or joining subqueries	Multiple OVER clauses with different partitions can coexist in the same SELECT

Feature comparison: GROUP BY vs. OVER(PARTITION BY ...)

⚖

WHEN TO USE WHICH

Use GROUP BY when you need a summary report where each group is a single row (e.g., total revenue per quarter for a dashboard). Use OVER(PARTITION BY ...) when you need each detail row annotated with its group's aggregate — for instance, displaying every transaction alongside its customer's lifetime spend, or computing each employee's salary as a percentage of the department budget.

SECTION 8

Connection to Advanced Window Function Features

Windowed aggregates with PARTITION BY form the foundation for a broader ecosystem of window functions defined in the SQL standard. Once the engine has established the partition-and-order infrastructure for an aggregate, the same machinery supports ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE), value functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE), and distribution functions (PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DISC). All of these share the same OVER clause syntax and partition-order semantics.

Feature	Windowed Aggregates (this lesson)	Advanced Window Functions
Functions	SUM, AVG, COUNT, MIN, MAX, etc.	ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, NTH_VALUE, NTILE, etc.
PARTITION BY	Optional (default = entire result set)	Optional (same default)
ORDER BY	Optional; changes aggregate from total to running/cumulative	Required for most (ranking/offset functions are meaningless without order)
Frame clause	Supported; enables moving averages, sliding sums	Supported by FIRST_VALUE, LAST_VALUE, NTH_VALUE; not applicable to ranking functions
Typical use case	Ratio-to-total, running totals, group annotations	Top-N per group, deduplication, gap analysis, sessionization

Windowed aggregates vs. advanced window functions

A solid understanding of PARTITION BY and the frame clause transfers directly to these advanced functions. For example, computing the second-highest salary per department uses DENSE_RANK() OVER(PARTITION BY dept ORDER BY salary DESC) and filtering where the rank equals 2 in an outer query. Detecting gaps in a time series uses LAG(event_time) OVER(PARTITION BY session_id ORDER BY event_time) to compare each row to its predecessor. In each case, PARTITION BY defines the scope and ORDER BY defines the direction — the same two concepts you have already mastered in the aggregate context.

💡

Performance Tip

When multiple window functions share the same PARTITION BY and ORDER BY, most optimizers compute them in a single sort-and-scan pass. Defining a reusable WINDOW clause (supported in PostgreSQL and SQL:2003-compliant engines) — e.g., WINDOW w AS (PARTITION BY dept ORDER BY hire_date) — improves readability and signals to the optimizer that the windows are identical.

SECTION 9

Practice Problems

The following problems use a table employees(emp_id INT, name VARCHAR, dept VARCHAR, salary DECIMAL, hire_date DATE). Assume the table contains at least 20 rows across 4 departments.

Question 1: CONCEPTUAL

Explain the difference between SELECT dept, AVG(salary) FROM employees GROUP BY dept and SELECT *, AVG(salary) OVER(PARTITION BY dept) FROM employees. In particular, describe the differences in output cardinality, the columns available in each result set, and one scenario where each approach is preferable.

Show answer

The GROUP BY query produces one row per department containing only dept and the average salary — all individual employee detail is collapsed. The OVER(PARTITION BY dept) query returns every row from the employees table with all original columns intact, plus an additional column showing the department average for that row's department. Use GROUP BY when building a summary dashboard that shows only department-level metrics. Use the windowed version when you need each employee's record annotated with their department's average — for instance, to identify employees earning above or below the department mean without an additional join.

Question 2: BASIC CALCULATION

Write a query that returns every employee's name, department, salary, and the total salary expenditure for their department. Use a windowed aggregate.

Show answer

The query is: SELECT name, dept, salary, SUM(salary) OVER(PARTITION BY dept) AS dept_total FROM employees; This uses SUM(salary) with OVER(PARTITION BY dept) to compute the department total without collapsing rows. Each employee row will show the same dept_total value as every other employee in the same department. No ORDER BY is needed inside the OVER clause because we want the full partition sum, not a running sum.

Question 3: INTERMEDIATE

Write a query that returns each employee's name, department, salary, the department average salary, and a column indicating whether the employee earns above or below the department average (label the column status with values 'Above Average', 'Below Average', or 'At Average'). You may use a CTE or subquery.

Show answer

Because window functions cannot be referenced in a WHERE or CASE in the same SELECT level (the window function result is not yet available when CASE is evaluated in some mental models, though technically both are in SELECT — the real issue is that referencing the alias is not allowed), the cleanest approach is a CTE:

WITH enriched AS (SELECT name, dept, salary, AVG(salary) OVER(PARTITION BY dept) AS dept_avg FROM employees) SELECT name, dept, salary, ROUND(dept_avg, 2) AS dept_avg, CASE WHEN salary > dept_avg THEN 'Above Average' WHEN salary < dept_avg THEN 'Below Average' ELSE 'At Average' END AS status FROM enriched;

The CTE first computes the windowed AVG and makes it available as a column; the outer query then uses a CASE expression to classify each employee. This two-layer pattern — CTE with window function, outer query with filter or conditional logic — is idiomatic and avoids the restriction against placing window functions in WHERE or CASE at the same query level as their definition.

Question 4: APPLIED

A finance team needs a report showing each employee's name, department, salary, their salary as a percentage of the department total, and their salary as a percentage of the company-wide total. Write a single query (no subqueries or CTEs) that computes both percentages.

Show answer

The key insight is that two OVER clauses can have different PARTITION BY definitions in the same SELECT:

SELECT name, dept, salary, ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept), 2) AS pct_of_dept, ROUND(100.0 * salary / SUM(salary) OVER(), 2) AS pct_of_company FROM employees ORDER BY dept, salary DESC;

The first SUM partitions by dept to get the department total; the second uses an empty OVER() to get the grand total across all employees. Dividing the row's salary by each total and multiplying by 100 yields the two percentages. The optimizer can compute both sums in one pass if the partition keys are compatible, though it may perform a separate scan for the grand total depending on the engine.

Question 5: CRITICAL THINKING

Consider the query: SELECT name, dept, salary, SUM(salary) OVER(PARTITION BY dept ORDER BY hire_date) AS running_total FROM employees; A developer notices that two employees in the same department with the same hire_date both show a running total that includes both of their salaries, rather than one showing a partial total. Explain why this happens, referencing the default frame clause, and rewrite the query so that each row gets a strictly row-by-row cumulative total (i.e., tied hire_dates are broken arbitrarily).

Show answer

When ORDER BY is specified without an explicit frame clause, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The RANGE keyword groups rows by value, not by physical position. Consequently, rows sharing the same hire_date (peer rows) are all included in each other's frames, so they all show the same running total that includes all peers. To produce a strictly sequential cumulative sum that processes one row at a time regardless of ties, change the frame to use ROWS instead of RANGE:

SELECT name, dept, salary, SUM(salary) OVER(PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM employees;

The ROWS keyword treats each physical row independently, so even if two rows share the same hire_date, the running total will accumulate one row at a time. The order among tied rows is non-deterministic unless a tiebreaker (e.g., emp_id) is added to ORDER BY.

SUMMARY

Summary

Windowed aggregates apply standard aggregate functions — SUM, AVG, COUNT, MIN, MAX — through an OVER(PARTITION BY ...) clause that divides the result set into partitions. Unlike GROUP BY, which collapses rows into group summaries, windowed aggregates preserve every original row and append the computed value as a new column. Adding ORDER BY inside the OVER clause converts a partition-wide total into a running aggregate whose default frame extends from the first row of the partition to the current row.

The frame clause (ROWS or RANGE BETWEEN) refines the window further for patterns like moving averages. Multiple OVER clauses with different PARTITION BY keys can coexist in the same SELECT, enabling powerful ratio-to-total and cross-level comparisons in a single query. These techniques form the gateway to the full SQL window function ecosystem — including ranking, offset, and distribution functions — all unified by the same OVER clause syntax.