Lesson 3 - Window Aggregate Functions

Welcome to Window Aggregate Functions#

You already know aggregate functions — SUM(), AVG(), COUNT(), MIN(), MAX(). You have used them with GROUP BY to summarize groups of rows. In this lesson you will use those same functions as window aggregate functions, computed over an OVER() window instead of a GROUP BY group.

This familiarity is a double-edged sword. The functions look identical, but the behavior differs in an important way: GROUP BY returns one row per group, while a window aggregate returns a value for every row. Getting clear on that difference — and on where window functions are and are not allowed in a query — is the goal of this lesson.

By the end of this lesson, you will be able to:

• Define window aggregate functions and how they differ from grouped aggregates
• Add window aggregates to non-aggregate queries to keep detail rows
• Combine window aggregates with GROUP BY queries
• Explain why window functions cannot appear in WHERE, and work around it with a subquery

This lesson builds on the OVER(), PARTITION BY, ORDER BY, and framing concepts from Lessons 1 and 2. Let’s dive in.

What Are Window Aggregate Functions?#

A window aggregate function is a regular aggregate (SUM, AVG, COUNT, and friends) applied over a window defined by OVER(). The function is the same; what changes is how it is applied and what comes back.

• With GROUP BY, the aggregate produces one result per group, and you lose the detail columns.
• With OVER(), the aggregate produces one result per row, and the detail columns stay.

Consider phone_sales_quantity, where each row is a model’s sales for a month:

sales_date | brand   | model                    | quantity
-----------+---------+--------------------------+---------
2022-01-31 | Samsung | Samsung Galaxy Z Fold4   | 40
2022-01-31 | Samsung | Samsung Galaxy S22 Ultra | 30
2022-02-28 | Samsung | Samsung Galaxy S22 Ultra | 35
2022-03-31 | Samsung | Samsung Galaxy S22 Ultra | 25
2022-03-31 | Samsung | Samsung Galaxy Z Fold4   | 60
2022-04-30 | Samsung | Samsung Galaxy Z Fold4   | 25
2022-05-31 | Samsung | Samsung Galaxy Z Fold4   | 30
2022-05-31 | Samsung | Samsung Galaxy S22 Ultra | 47
2022-06-30 | Samsung | Samsung Galaxy Z Fold4   | 76

The grouped version totals and averages quantity per date but drops model and quantity:

SELECT sales_date, SUM(quantity), AVG(quantity)
  FROM phone_sales_quantity
 GROUP BY sales_date;

If you try to keep quantity in that query, the database rejects it:

SELECT sales_date, quantity, SUM(quantity), AVG(quantity)
  FROM phone_sales_quantity
 GROUP BY sales_date;

ERROR: column "phone_sales_quantity.quantity" must appear in the GROUP BY clause or be used in an aggregate function.

The window version, by contrast, keeps every detail column and attaches the per-date aggregate to each row:

SELECT sales_date, brand, model, quantity,
       SUM(quantity) OVER(PARTITION BY sales_date),
       AVG(quantity) OVER(PARTITION BY sales_date)
  FROM phone_sales_quantity;

sales_date | brand   | model                    | quantity | sum | avg
-----------+---------+--------------------------+----------+-----+-----
2022-01-31 | Samsung | Samsung Galaxy Z Fold4   | 40       | 70  | 35.0
2022-01-31 | Samsung | Samsung Galaxy S22 Ultra | 30       | 70  | 35.0
2022-02-28 | Samsung | Samsung Galaxy S22 Ultra | 35       | 35  | 35.0
2022-03-31 | Samsung | Samsung Galaxy S22 Ultra | 25       | 85  | 42.5
2022-03-31 | Samsung | Samsung Galaxy Z Fold4   | 60       | 85  | 42.5
2022-04-30 | Samsung | Samsung Galaxy Z Fold4   | 25       | 25  | 25.0
2022-05-31 | Samsung | Samsung Galaxy Z Fold4   | 30       | 77  | 38.5
2022-05-31 | Samsung | Samsung Galaxy S22 Ultra | 47       | 77  | 38.5
2022-06-30 | Samsung | Samsung Galaxy Z Fold4   | 76       | 76  | 76.0

Because each row keeps its own quantity alongside the monthly average, you can compare the two directly — for example, quantity - AVG(quantity) OVER(PARTITION BY sales_date) tells you how far each model’s sales sat above or below the month’s average. That comparison is awkward with GROUP BY but trivial with a window function.

The Window Aggregate Functions#

Standard SQL window aggregates support partitioning, ordering, and framing. The general shape is:

aggregate_function(column_name) OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [frame clause]
)

The common window aggregate functions are:

A Company-Wide Total Without Losing Detail#

Using the employees table from Lesson 1, a plain aggregate gives the total payroll as a single number:

SELECT SUM(salary) AS company_salary
  FROM employees;

company_salary
--------------
751800

To keep every employee’s details next to that total, switch to a window aggregate with an empty OVER():

SELECT *,
       SUM(salary) OVER() AS company_salary
  FROM employees;

Every row now carries company_salary = 751800.

Per-Group Extremes with PARTITION BY#

To show each department’s highest and lowest salary on every row, partition by department:

SELECT last_name, department, salary,
       MAX(salary) OVER(PARTITION BY department) AS max_salary,
       MIN(salary) OVER(PARTITION BY department) AS min_salary
  FROM employees;

last_name | department | salary    | max_salary | min_salary
----------+------------+-----------+------------+-----------
Mitchell  | IT         | 89900.00  | 89900      | 67800
John      | IT         | 75900.00  | 89900      | 67800
Callahan  | IT         | 78000.00  | 89900      | 67800
King      | IT         | 67800.00  | 89900      | 67800
Adams     | Management | 108000.00 | 108000     | 108000
Johnson   | Sales      | 76500.00  | 98900      | 69800
Park      | Sales      | 69800.00  | 98900      | 69800
Peacock   | Sales      | 87000.00  | 98900      | 69800
Edwards   | Sales      | 98900.00  | 98900      | 69800

Running and Moving Averages#

Add ORDER BY and a frame to turn these aggregates into running and moving metrics. For a table phone_sales_by_month with quantity and unit_price, this computes both a running average revenue and a rolling three-month average revenue per brand:

SELECT *,
       AVG(quantity * unit_price) OVER(PARTITION BY brand
            ORDER BY sales_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_average,
       AVG(quantity * unit_price) OVER(PARTITION BY brand
            ORDER BY sales_date
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS three_month_average
  FROM phone_sales_by_month;

The first window accumulates from the start of each brand to the current month; the second looks only at the current month and the two before it.

Window Functions and the WHERE Clause#

Window functions have a strict rule about where they can appear in a query, and it trips up nearly everyone at first.

You can freely use a window function in the SELECT list of a filtered query. The window is computed over whatever rows survive the WHERE clause:

SELECT first_name, last_name, department, salary,
       AVG(salary) OVER() AS average_salary
  FROM employees
 WHERE department = 'Sales';

first_name | last_name | department | salary   | average_salary
-----------+-----------+------------+----------+---------------
Nancy      | Edwards   | Sales      | 98900.00 | 83050
Jane       | Peacock   | Sales      | 87000.00 | 83050
Margaret   | Park      | Sales      | 69800.00 | 83050
Steve      | Johnson   | Sales      | 76500.00 | 83050

The average_salary here is 83050 — the average of only the Sales rows, because the window function runs after WHERE has filtered the data.

But you cannot put a window function inside the WHERE clause itself:

SELECT first_name, last_name, department, salary,
       AVG(salary) OVER() AS average_salary
  FROM employees
 WHERE salary > AVG(salary) OVER();

ERROR: window functions are not allowed in WHERE

Why: The Order of Execution#

SQL processes a query in a fixed logical order:

1. FROM / JOIN — build the data set
2. WHERE — filter rows
3. GROUP BY — form groups
4. HAVING — filter groups
5. SELECT — choose columns; this is where window functions run
6. ORDER BY — sort the result
7. LIMIT / OFFSET — trim the result

Window functions are evaluated at the SELECT step. By the time WHERE, GROUP BY, and HAVING are being processed, the window results do not exist yet — so they cannot be referenced there. Putting a window function in WHERE would create a circular dependency, which is why the database refuses.

The Fix: A Subquery or CTE#

To filter on a window result, compute it in a subquery (or CTE) first, then filter the outer query:

SELECT first_name, last_name, salary
  FROM (SELECT first_name, last_name, salary,
               AVG(salary) OVER() AS average_salary
          FROM employees) AS subquery
 WHERE salary > average_salary;

The inner query produces average_salary as an ordinary column; the outer query can then filter on it normally. This subquery-then-filter pattern is the standard way to keep only rows that beat a windowed value.

Combining Window Aggregates with Aggregate Queries#

You can mix a window aggregate into a query that already uses GROUP BY, but it requires care. Suppose you want each department’s total salary and the company’s total salary on the same rows:

SELECT department, SUM(salary) AS total_department_salary,
       SUM(salary) OVER() AS total_company_salary
  FROM employees
 GROUP BY department;

ERROR: column "employees.salary" must appear in the GROUP BY clause or be used in an aggregate function

The problem: after GROUP BY department, the raw salary column no longer exists as an individual value — only department and aggregated expressions are available. A window function in this query must operate on those same allowed building blocks. The fix is to apply the window function to the already-aggregated value:

SELECT department, SUM(salary) AS total_department_salary,
       SUM(SUM(salary)) OVER() AS total_company_salary
  FROM employees
 GROUP BY department;

department | total_department_salary | total_company_salary
-----------+-------------------------+---------------------
Management | 108000                  | 751800
Sales      | 332200                  | 751800
IT         | 311600                  | 751800

The inner SUM(salary) totals each department; the outer SUM(...) OVER() then sums those department totals across the whole company. SQL does not allow plain nested aggregates, but here the window function is summing an aggregate, which is permitted. The rule to remember: in a grouped query, any expression inside a window function must either be in the GROUP BY clause or be wrapped in an aggregate.

Practice Exercises#

Use the phone_sales_quantity, phone_sales_by_month, and employees tables.

Exercise 1: Compare to the Monthly Average#

Against phone_sales_quantity, return all original columns, the average daily sales quantity (aliased average), and the difference between each row’s quantity and that average (aliased difference).

-- Your code here

Exercise 2: Above-Average Earners#

Return the first_name, last_name, and salary of employees whose salary is above the company average. Remember the WHERE rule.

Exercise 3: Monthly and Total Brand Revenue#

Against phone_sales_by_month, return the month (from sales_date), brand, the monthly revenue per brand (aliased monthly_brand_revenue), and the total revenue per brand (aliased total_brand_revenue). Group by month and brand; revenue is quantity * unit_price.

Summary#

You used aggregate functions as window functions, computing SUM, AVG, MIN, MAX, and COUNT over an OVER() window so that every detail row keeps its data alongside the aggregate. You saw why window functions run at the SELECT step — and therefore cannot appear in WHERE, GROUP BY, or HAVING — and how a subquery or CTE lets you filter on a windowed value. Finally, you combined window aggregates with grouped queries by applying the window function to an already-aggregated expression.

Key Concepts#

• Window aggregate function — an aggregate computed over an OVER() window, returning one value per row instead of one per group.
• Detail plus aggregate — window aggregates let you keep raw columns next to summary values, enabling direct row-to-group comparisons.
• Order of execution — window functions are evaluated at the SELECT step, which is why they are forbidden in WHERE/GROUP BY/HAVING.
• Subquery filter — compute the window value in an inner query, then filter on it in the outer query.
• Aggregate of an aggregate — SUM(SUM(x)) OVER() is valid in a grouped query because the window function operates on the grouped result.

Why This Matters#

Window aggregates are the bread and butter of analytical SQL. Comparing a value to its group’s average, building running totals and rolling averages, and adding a grand total beside per-group subtotals are everyday tasks in reporting and dashboards. Understanding the order of execution also saves you hours of confusion the first time the database refuses a window function in WHERE — you will know immediately to reach for a subquery.

Next Steps#

Continue Building Your Skills#

Try rewriting a few of your old GROUP BY queries as window-aggregate queries and notice how much more you can show in a single result. Then practice the subquery-filter pattern until it is automatic — it unlocks “keep only the rows above the average” and countless variations. These patterns appear in nearly every analytics task you will face.