Window Functions & GROUP BY — Avoid mixing window functions...

SECTION 1

Historical Context & Motivation

The relational model, proposed by E.F. Codd in 1970, initially provided only basic aggregation through what would become the GROUP BY clause. GROUP BY collapses rows into summary groups, a powerful but inherently destructive operation—once rows are aggregated, the original row-level detail is lost. For decades, analysts worked around this limitation using self-joins and correlated subqueries, but these approaches were verbose, error-prone, and often inefficient. The SQL standard needed a mechanism to compute aggregates without collapsing the result set, and that need ultimately gave rise to window functions.

1986

SQL-86 Standard

The first ANSI SQL standard formalized SELECT, FROM, WHERE, and GROUP BY. Aggregation required collapsing rows—there was no way to compute running totals or rankings while preserving row-level detail.

1999

SQL:1999 — OLAP Extensions Proposed

The SQL:1999 standard introduced common table expressions and recursive queries, but window functions were still absent. OLAP vendors like Oracle began shipping proprietary analytic functions.

2003

SQL:2003 — Window Functions Standardized

The OVER clause, PARTITION BY, ORDER BY within windows, and ranking functions (ROW_NUMBER, RANK, DENSE_RANK) were formally standardized. This created a new logical processing phase distinct from GROUP BY.

2012

PostgreSQL 8.4–9.x Adoption

PostgreSQL became one of the first open-source databases to offer robust window function support. This accelerated adoption in academia and industry, but also exposed a common class of bugs: mixing window functions with GROUP BY incorrectly.

2020s

Ubiquitous Window Functions

Today, every major RDBMS (PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite 3.25+) supports window functions. Understanding how they interact with GROUP BY is now a core competency tested in technical interviews and database courses.

The central question this lesson addresses is deceptively simple: if both GROUP BY and window functions perform aggregation, what happens when you use them together in the same query? The answer lies in SQL's logical order of operations, which dictates that GROUP BY executes before window functions. Misunderstanding this order is the root cause of a pervasive class of SQL bugs.

SECTION 2

Core Principles & Definitions

To reason correctly about queries that combine GROUP BY and window functions, you must internalize three foundational ideas: the logical query execution order, the distinction between aggregate and window contexts, and the concept of the virtual table that exists at each processing stage. These principles are not implementation details—they are part of the SQL standard's semantic model, and every conforming database engine must behave as if it follows this order, regardless of internal optimizations.

Logical Execution Order

SQL processes clauses in a fixed logical order: FROM → WHERE → GROUP BY → HAVING → SELECT (including window functions) → ORDER BY → LIMIT. Window functions evaluate after GROUP BY has already collapsed rows.

GROUP BY Collapses Rows

GROUP BY reduces the result set so that each group becomes exactly one row. Any column in SELECT must either be in the GROUP BY list or wrapped in an aggregate function. The original row-level detail is irrecoverable at this point.

Window Functions Preserve Rows

Window functions compute values across a set of rows related to the current row, but they never collapse the result set. They add information to each row without removing any rows from the output.

The 'Input' to Window Functions

When GROUP BY is present, window functions operate on the already-grouped result. They see the collapsed rows, not the original detail rows. This means PARTITION BY and ORDER BY in the OVER clause reference grouped columns and aggregate results.

You Cannot Filter on Window Functions in WHERE or HAVING

Because window functions execute during the SELECT phase, you cannot reference them in WHERE (too early) or HAVING (also too early). To filter on a window function result, wrap the query in a CTE or subquery.

✦ KEY TAKEAWAY

Think of SQL query execution like a factory assembly line. GROUP BY is the compactor that crushes raw materials into dense blocks. Window functions are the quality inspector who walks along the conveyor belt after compaction, measuring and labeling each block. The inspector never sees the original raw materials—only the compacted blocks. If you expected the inspector to measure the raw materials, you placed them at the wrong station on the line.

SECTION 3

Visual Explanation: SQL Logical Execution Pipeline

The pipeline diagram shows the six major logical phases. Notice that GROUP BY (phase 3) collapses 6 detail rows into 2 grouped rows before the SELECT + Window Functions phase (phase 5) even begins. The inset panel shows the concrete data at each stage: the window function never has access to the 6 original rows.

The diagram above is the single most important mental model for this topic. When a query contains both GROUP BY and a window function, the window function does not operate on the original table—it operates on the post-aggregation virtual table. If you write SUM(amount) OVER (PARTITION BY dept) alongside GROUP BY dept, the PARTITION BY is partitioning over the already-grouped rows—each of which already represents an entire department. This is almost never the intent, and it produces a result identical to the aggregate SUM(amount) for that group. Understanding this pipeline eliminates an entire category of SQL errors.

SECTION 4

How the Interaction Works — Step by Step

Let us formalize what happens when a query combines GROUP BY with a window function. Consider a table orders(order_id, region, product, amount) and the following query:

⚠ THE PROBLEMATIC QUERY

SELECT region, SUM(amount) AS total, ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS rank FROM orders GROUP BY region;

This query actually works correctly—but not for the reason most beginners assume. Let us trace through the logical execution to see precisely why.

Phase-by-Phase Trace

Logical Execution Trace

Phase 1 — FROMThe engine loads all rows from the orders table. Suppose there are 1,000 rows across 4 regions.

Virtual table: 1,000 rows × 4 columns

Phase 2 — WHERENo WHERE clause is present, so all 1,000 rows pass through unfiltered.

Virtual table: still 1,000 rows

Phase 3 — GROUP BY regionThe 1,000 rows are collapsed into 4 groups—one per distinct region value. From this point forward, only 4 rows exist in the virtual table. Each row carries the grouped column (region) and any aggregate expressions (SUM(amount)). Individual order rows are gone.

Virtual table: 4 rows (one per region)

Phase 4 — HAVINGNo HAVING clause, so all 4 grouped rows survive.

Phase 5 — SELECT + Window FunctionsNow the engine evaluates ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC). Critically, this window function sees only the 4 grouped rows. It ranks them 1 through 4 by their aggregate totals. Since SUM(amount) was already computed during GROUP BY, it is available as a column in the virtual table. The result here is actually sensible—we are ranking regions by total sales.

Output: 4 rows, each with region, total, and rank

When It Goes Wrong

The confusion arises when developers intend the window function to operate on the pre-aggregation detail rows. For example, suppose you want to rank individual orders within each region, but you also want the region total in the same output. Writing ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) inside a query that also has GROUP BY region will not rank individual orders—it will attempt to rank the single grouped row per region, producing a rank of 1 for every region. The solution is to separate the two concerns using a subquery or CTE: compute the window function on the detail rows in an inner query, then aggregate in an outer query, or vice versa.

SECTION 5

Common Patterns and Anti-Patterns

The interaction between GROUP BY and window functions falls into three categories: correct and intentional usage, syntactically valid but semantically wrong queries, and outright errors that the database engine rejects. The second category is the most dangerous because the query runs without error but produces misleading results.

Three columns compare correct patterns (left, green), misleading but syntactically valid anti-patterns (center, amber), and outright errors (right, red). The center column is the most dangerous because the query executes without error yet produces unexpected results.

The most instructive case is Anti-Pattern A in the center column. When you write SUM(sal) OVER (PARTITION BY dept) in a query grouped by dept, the window function partitions the post-GROUP BY result set by department. Since GROUP BY already guarantees one row per department, every partition contains exactly one row. The window SUM over a single-row partition simply returns the value in that row—making the window function entirely redundant. This kind of code smells fine during code review but delivers no additional analytical power and can mislead readers about the query's semantics.

💡 THE NESTED AGGREGATE TRICK

Pattern B in the correct column shows SUM(SUM(rev)) OVER (ORDER BY month). This is not a typo. The inner SUM(rev) is the GROUP BY aggregate (monthly revenue). The outer SUM(...) OVER (...) is the window function that computes a running total of those monthly sums. This nested syntax is the legitimate way to apply a window function to an aggregate result within the same query.

SECTION 6

Worked Example: Fixing a Broken Query

A developer wants to produce a report from a sales(sale_id, region, rep_name, amount, sale_date) table. The report should show each sales representative, their total sales, and their rank within their region. The developer writes this query:

🐛 BUGGY QUERY

SELECT region, rep_name, SUM(amount) AS total_sales, RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS region_rank FROM sales GROUP BY region;

Diagnosing and Fixing the Query

Step 1 — Identify the BugThe GROUP BY clause groups by region alone, but the SELECT list includes rep_name, which is neither in the GROUP BY list nor wrapped in an aggregate function. In PostgreSQL or SQL Server, this query will fail with an error. In MySQL with permissive ONLY_FULL_GROUP_BY disabled, it will silently return an arbitrary representative name per region—not the intended behavior.

Bug: rep_name is not in GROUP BY and not aggregated

Step 2 — Determine the Correct Grouping LevelThe developer wants to rank individual representatives within each region. This means the grouping level must be (region, rep_name) so that each rep gets their own row with their total sales. The window function will then partition by region and rank over those per-rep rows.

Required GROUP BY: region, rep_name

Step 3 — Write the Fixed QueryBy adding rep_name to the GROUP BY clause, we ensure each (region, rep_name) pair becomes one row. The window function then sees multiple rows per region partition and can meaningfully rank them.

Step 4 — Verify the Fixed Query

SELECT region, rep_name, SUM(amount) AS total_sales, RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS region_rank FROM sales GROUP BY region, rep_name;

Correct: Each rep gets their own row; RANK partitions over multiple reps per region

Step 5 — Trace the Logical ExecutionIf there are 20 reps across 4 regions, GROUP BY (region, rep_name) produces 20 rows. The window function PARTITION BY region creates 4 partitions, each containing roughly 5 reps. RANK orders them by SUM(amount) DESC within each partition, producing meaningful ranks from 1 to ~5 per region. This is exactly the intent.

20 rows output, each with a meaningful region_rank

🔍 DIAGNOSTIC RULE

When debugging a query that mixes GROUP BY and window functions, ask yourself: how many rows does GROUP BY produce, and is that the number of rows I want the window function to see? If the answer is no, your GROUP BY granularity is wrong—either too coarse (losing detail the window function needs) or missing entirely (you may not need GROUP BY at all and should use only the window function).

SECTION 7

GROUP BY vs. Window Functions — When to Use Which

Choosing between GROUP BY and window functions—or deciding to combine them—depends on what the final result set should look like. The following comparison table highlights the key differences across several dimensions that matter when designing queries.

Comparison of GROUP BY and window functions across key dimensions

Dimension	GROUP BY	Window Function
Row count	Reduces rows — one output row per group	Preserves rows — one output row per input row
Detail access	Original row-level columns are lost unless included in GROUP BY	All original columns remain accessible in SELECT
Execution phase	Phase 3 (before HAVING and SELECT)	Phase 5 (during SELECT, after GROUP BY)
Ranking capability	Not directly — requires a subquery or self-join	Native — ROW_NUMBER, RANK, DENSE_RANK, NTILE
Running totals	Not directly — requires correlated subquery	Native — SUM(...) OVER (ORDER BY ...)
Use with HAVING	Yes — HAVING filters groups after aggregation	No — window results cannot be referenced in HAVING
Combining both	Produces the grouped result set	Operates on the grouped result set — sees only grouped rows

🧭 DECISION HEURISTIC

Use GROUP BY when your report requires summary rows (e.g., total per department). Use window functions when your report requires detail rows with context (e.g., each employee's salary alongside their department average). Combine them when you need to enrich summary rows with cross-group analytics (e.g., ranking departments by total salary). In that last case, always verify that the GROUP BY granularity matches what the window function should see.

SECTION 8

Connection to Advanced Patterns

Understanding how GROUP BY and window functions interact is a prerequisite for several advanced SQL patterns. As you progress, you will encounter scenarios where the basic principles discussed here extend into more complex territory, including nested window functions, multiple levels of aggregation, and the use of Common Table Expressions (CTEs) to separate aggregation from windowing across query layers.

How this lesson's concepts connect to advanced SQL patterns

This Lesson's Concept	Advanced Extension
Window fn sees grouped rows	Multi-layer CTEs: first CTE groups, second CTE applies window fn, third CTE groups again — each layer operates on the previous layer's output
SUM(SUM(x)) OVER (...) nested aggregate	GROUPING SETS, CUBE, ROLLUP produce multiple aggregation levels in one query; window functions on top of these require careful reasoning about which level each row represents
Cannot filter on window fn in WHERE/HAVING	QUALIFY clause (Snowflake, BigQuery, DuckDB) — a new logical phase after SELECT that filters on window function results without requiring a CTE wrapper
PARTITION BY on grouped columns	Named window definitions (WINDOW clause) allow reusable window specs across multiple window functions in the same SELECT, reducing redundancy and errors
Logical execution order awareness	Query plan analysis — understanding physical execution via EXPLAIN ANALYZE and how the optimizer may reorder or merge GROUP BY and window computations for performance

The QUALIFY clause deserves special mention. In standard SQL (as of 2023), there is no QUALIFY clause—it is a vendor extension. Its adoption is growing rapidly because it directly addresses the frustration of wrapping queries in CTEs just to filter on window function results. If you are working with Snowflake, Google BigQuery, or DuckDB, QUALIFY is available and eliminates one of the most common reasons developers incorrectly try to put window functions in HAVING. In PostgreSQL and SQL Server, you still need the CTE approach, making it even more important to internalize the logical execution order.

SECTION 9

Practice Problems

PROBLEM 1 — CONCEPTUAL

Explain in your own words why a window function that uses PARTITION BY dept in a query that also has GROUP BY dept creates partitions of exactly one row each. Why does this make the window function's computation trivial?

PROBLEM 2 — BASIC CALCULATION

Given a table orders(order_id, customer_id, amount), write a query that returns each customer's total spending and their rank among all customers by total spending (highest first). State clearly whether you need GROUP BY, a window function, or both.

PROBLEM 3 — INTERMEDIATE

A colleague writes the following query and is confused by the output. Identify the problem and provide the corrected query.

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

PROBLEM 4 — APPLIED

A data analyst needs a monthly revenue report from a transactions(txn_id, txn_date, amount) table. The report should show: (1) the month, (2) total revenue for that month, (3) a cumulative year-to-date revenue. Write the query, explaining why the nested aggregate pattern SUM(SUM(amount)) is necessary.

PROBLEM 5 — CRITICAL THINKING

Consider a scenario where you need to find the top-selling product in each category, where 'top-selling' means the product with the highest total sales amount. You might be tempted to use GROUP BY category, product with a window function and then filter. Discuss why you cannot use HAVING to filter on the window function result, propose a correct solution using a CTE, and explain how the QUALIFY clause (in databases that support it) simplifies this pattern.

SUMMARY

Lesson Summary

The interaction between GROUP BY and window functions is governed entirely by SQL's logical execution order: FROM → WHERE → GROUP BY → HAVING → SELECT (window functions) → ORDER BY → LIMIT. Because GROUP BY executes before window functions, the window function always operates on the post-aggregation virtual table, never on the original detail rows. This means that PARTITION BY on the same column as GROUP BY creates single-row partitions, making the window function redundant. To apply a window function to detail rows, either remove GROUP BY or use a CTE to separate the two operations.

When you intentionally combine both, use the nested aggregate pattern such as SUM(SUM(x)) OVER (...) where the inner aggregate is the GROUP BY computation and the outer function is the window computation. Always verify your query's correctness by asking: how many rows does GROUP BY produce, and is that the granularity the window function needs? If the answer is no, restructure your query. Remember that window function results cannot be filtered in WHERE or HAVING—use a CTE or the QUALIFY clause (where available) instead.