Most developers use PARTITION BY without fully understanding what it's doing to their data — or why omitting ORDER BY is sometimes exactly right. This lesson builds a precise mental model of window partitioning from first principles, using real reporting scenarios to show when and why each choice matters.
Imagine you're analyzing sales data for a retail chain. Your manager wants to know not just each store's total revenue, but how each store's revenue compares to the regional average — all in a single query result that still shows individual transaction rows. If you reach for a standard GROUP BY, you immediately hit a wall: aggregation collapses your rows, so you can't see individual transactions anymore. If you join a subquery, you end up with awkward, hard-to-maintain SQL. This is exactly the problem that window functions — and specifically PARTITION BY — were designed to solve.
By the end of this lesson, you'll understand what partitioning actually means inside a SQL engine, how PARTITION BY controls the scope of your window calculations, and — crucially — when it makes perfect sense to use a window function without ORDER BY. These distinctions separate developers who can write window functions from developers who truly understand them.
What you'll learn:
GROUP BY aggregationPARTITION BY logically divides your dataset into independent groups for calculationORDER BY inside a window function is required, optional, or actively misleadingYou should be comfortable with basic SELECT statements, GROUP BY aggregation, and joins. Some exposure to aggregate functions like SUM, AVG, and COUNT is assumed. If you've never seen a window function before, consider reading Window Functions: RANK, ROW_NUMBER, and LAG first — that article covers the foundational syntax this lesson builds on.
Before we can talk about partitioning, we need to fix a clear mental model of what a window function actually does.
When you write a GROUP BY query, SQL collapses multiple rows into one. Each group becomes a single output row. You trade row-level detail for aggregate insight. That's often useful, but it's a one-way transformation — once you've grouped, the individual rows are gone.
A window function is different. It computes an aggregate or ranking across a set of rows, but it writes the result back onto every individual row rather than collapsing them. The "window" is the set of rows the function looks at when computing its result for any given row.
Think of it like a sliding pane of glass you hold up to your dataset. For each row you're currently looking at, the window defines which rows are visible through that pane. The function computes its value from whatever rows appear in the window and places that result in the current row's output — without disturbing any other rows.
The syntax for a window function always includes the OVER clause:
some_function() OVER (
PARTITION BY some_column
ORDER BY another_column
ROWS BETWEEN ...
)
The OVER keyword is what makes a function a window function. Everything inside the parentheses defines the shape and behavior of the window. PARTITION BY and ORDER BY are both optional components of that definition — and understanding when to use each one (or neither) is what this lesson is about.
PARTITION BY is the clause that splits your dataset into independent subsets before the window function does its work. Each subset — called a partition — is treated as its own isolated universe for the purpose of the calculation.
Here's the key insight that most explanations gloss over: partitioning does not reduce rows. Every row still appears in the output. What partitioning does is limit the scope of calculation so that each row's window only includes other rows from the same partition.
Let's make this concrete. Suppose you have a sales table:
CREATE TABLE sales (
sale_id INT,
store_id INT,
region VARCHAR(50),
amount DECIMAL(10,2),
sale_date DATE
);
And it contains data like this:
| sale_id | store_id | region | amount | sale_date |
|---|---|---|---|---|
| 1 | 101 | East | 450.00 | 2024-01-05 |
| 2 | 101 | East | 320.00 | 2024-01-06 |
| 3 | 102 | East | 780.00 | 2024-01-07 |
| 4 | 201 | West | 530.00 | 2024-01-05 |
| 5 | 201 | West | 290.00 | 2024-01-08 |
| 6 | 202 | West | 640.00 | 2024-01-09 |
Now you want to show, for each sale, what percentage of its region's total revenue that individual sale represents. With PARTITION BY:
SELECT
sale_id,
store_id,
region,
amount,
SUM(amount) OVER (PARTITION BY region) AS region_total,
ROUND(
amount / SUM(amount) OVER (PARTITION BY region) * 100,
2
) AS pct_of_region
FROM sales;
The result:
| sale_id | store_id | region | amount | region_total | pct_of_region |
|---|---|---|---|---|---|
| 1 | 101 | East | 450.00 | 1550.00 | 29.03 |
| 2 | 101 | East | 320.00 | 1550.00 | 20.65 |
| 3 | 102 | East | 780.00 | 1550.00 | 50.32 |
| 4 | 201 | West | 530.00 | 1460.00 | 36.30 |
| 5 | 201 | West | 290.00 | 1460.00 | 19.86 |
| 6 | 202 | West | 640.00 | 1460.00 | 43.84 |
Notice what happened. The East rows all show 1550.00 as their region_total — that's the sum of only the East rows. The West rows show 1460.00 — the sum of only the West rows. Meanwhile, all six original rows are still present. No collapsing. No joining. The partition boundary is completely invisible in the output except through its effect on the calculated values.
Key insight
PARTITION BY is conceptually similar to GROUP BY, but instead of collapsing rows into one per group, it stamps the group-level calculation onto every row in the group. The rows remain intact; only the calculation scope changes.
Understanding how the database processes a partitioned window function helps you write better queries and debug unexpected results.
When your query runs, the database handles window functions in a specific sequence:
WHERE) — eliminate rows that don't belong in the resultGROUP BY, HAVING) — if presentOVER) — computed on the result set after all filtering and groupingHAVING on window results, via a subquery or CTE)ORDER BY, LIMIT) — final presentationThis ordering matters. Window functions operate on the rows as they exist after your WHERE clause has run. If you filter out rows, those rows don't participate in partition calculations. So if you wrote WHERE region = 'East', the SUM(amount) OVER (PARTITION BY region) would still calculate correctly for East — but West partitions simply wouldn't exist.
Warning
You cannot filter on a window function result in the same WHERE clause where it's computed. The window runs after filtering, so SQL won't let you write WHERE pct_of_region > 30 in the same query that defines pct_of_region. You need to wrap the query in a Common Table Expression (CTE) or subquery and filter in the outer level.
Here's how to correctly filter on a window result:
WITH regional_analysis AS (
SELECT
sale_id,
store_id,
region,
amount,
ROUND(
amount / SUM(amount) OVER (PARTITION BY region) * 100,
2
) AS pct_of_region
FROM sales
)
SELECT *
FROM regional_analysis
WHERE pct_of_region > 30;
This is where many developers get confused. They see window functions almost always written with ORDER BY and assume it's mandatory. It isn't — and sometimes including it would actually break your intended logic.
The rule is simple but important: ORDER BY inside a window function affects the window frame, which defines which rows are included in the calculation for each row. When you omit ORDER BY, the window frame defaults to the entire partition. Every row in the partition contributes to every other row's calculation.
That default behavior is precisely what you want for the class of calculations known as partition-wide aggregations — things like:
For all of these, you want every row in the partition to count. Adding ORDER BY would change the frame to a running calculation, which is a completely different thing.
Let's contrast the two approaches clearly:
-- What you want: each row sees the TOTAL sum for its region
SELECT
sale_id,
region,
amount,
SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;
-- What you probably don't want here: each row sees a RUNNING sum
SELECT
sale_id,
region,
amount,
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total
FROM sales;
The first query gives every East row the same region_total: 1550.00. That's the partition-wide sum.
The second query gives each East row a cumulative total up to and including that row's sale_date. The first East row might show 450.00, the second 770.00, the third 1550.00. Useful in a different scenario, but meaningfully different in behavior.
Tip
A good mental test: ask yourself "Should this calculation produce the same value for every row in the partition?" If yes, omit ORDER BY. If the value should change as you move through the partition, you need ORDER BY.
For a deeper dive into how ORDER BY interacts with window frames, see Advanced Window Frame Specifications: ROWS, RANGE, and GROUPS Clauses for Precise Rolling Calculations.
You're not limited to partitioning on a single column. PARTITION BY accepts a comma-separated list of columns, just like GROUP BY. The partition is then defined by the unique combination of values across all listed columns.
Suppose you want each sale to show the average revenue for its specific store within its region — a finer-grained view than the region-wide average:
SELECT
sale_id,
store_id,
region,
amount,
AVG(amount) OVER (PARTITION BY region, store_id) AS store_avg_in_region,
AVG(amount) OVER (PARTITION BY region) AS region_avg
FROM sales;
Here, PARTITION BY region, store_id creates one partition per unique region-store combination. Store 101 in the East gets its own partition (two rows), store 102 in the East gets its own (one row), and so on.
This is a technique you'll use constantly in real reporting work. It lets you simultaneously answer "how does this row compare within its smallest natural group?" and "how does that group itself compare to the broader category?"
What if you omit PARTITION BY altogether but keep the OVER clause? The window expands to encompass the entire result set. Every row is in a single, undivided partition.
SELECT
sale_id,
region,
amount,
SUM(amount) OVER () AS grand_total,
ROUND(amount / SUM(amount) OVER () * 100, 2) AS pct_of_grand_total
FROM sales;
Every row gets the same grand_total: the sum of all amounts across all regions. This is a simple, often underused technique for showing each row's contribution to the overall dataset without a separate subquery.
Note
An empty OVER () clause is entirely valid SQL. It means "compute this function over all rows in the result set, with no partitioning and no ordering." It's the simplest possible window definition.
You can combine partition-level and grand-total windows in the same query to build a two-level comparison:
SELECT
sale_id,
region,
amount,
SUM(amount) OVER (PARTITION BY region) AS region_total,
SUM(amount) OVER () AS grand_total,
ROUND(
SUM(amount) OVER (PARTITION BY region) /
SUM(amount) OVER () * 100,
2
) AS region_pct_of_grand
FROM sales;
This approach mirrors reporting patterns you'll often need for dashboards, executive summaries, and cohort analysis work.
Let's walk through a fuller example that you'd actually encounter in practice. You're working with an HR dataset and need to produce a compensation report showing each employee alongside their department's average salary and how they compare to it.
CREATE TABLE employees (
employee_id INT,
full_name VARCHAR(100),
department VARCHAR(50),
job_title VARCHAR(50),
salary DECIMAL(12,2),
hire_date DATE
);
The business questions are:
SELECT
employee_id,
full_name,
department,
job_title,
salary,
ROUND(AVG(salary) OVER (PARTITION BY department), 2) AS dept_avg_salary,
ROUND(salary - AVG(salary) OVER (PARTITION BY department), 2) AS diff_from_avg,
COUNT(*) OVER (PARTITION BY department) AS dept_headcount,
ROUND(
salary / SUM(salary) OVER (PARTITION BY department) * 100,
2
) AS pct_of_dept_budget
FROM employees
ORDER BY department, salary DESC;
Notice that all four window expressions use PARTITION BY department with no ORDER BY. That's intentional — none of these calculations are running totals or rankings. They're all partition-wide facts that should be the same for every row they're stamped onto, except for the individual salary values in the numerator.
Tip
When you're writing multiple window functions that share the same PARTITION BY clause, most databases let you define the window once using the WINDOW keyword and reference it by name. This keeps your SQL readable and avoids repeating the partition definition: WINDOW dept_window AS (PARTITION BY department). Then use AVG(salary) OVER dept_window.
Work through these queries using the sales table from earlier in this lesson. If you have a local database, create the table and insert the sample rows. If not, most online SQL playgrounds (like db-fiddle.com or SQLFiddle) will work fine.
Exercise 1: Write a query that shows each sale alongside:
region_max)region_min)Exercise 2: Extend the query to add a column showing each sale as a percentage of the grand total across all regions combined — not just its own region.
Exercise 3: Modify the partition to use both region and store_id. Observe how region_total values change compared to Exercise 1. Write a sentence explaining why store 102's row now shows a different value than it did when you partitioned only by region.
Challenge: Using a CTE, filter the results of Exercise 1 to show only sales where the amount is above the region maximum minus 100. (This requires wrapping the window function in a CTE first — if you're not sure why, revisit the warning earlier about filtering on window results.)
If you write GROUP BY region and PARTITION BY region in the same context and expect them to behave the same, you'll be surprised. GROUP BY collapses. PARTITION BY doesn't. Use GROUP BY when you want one row per group. Use PARTITION BY when you want group-level facts on individual rows.
This is the most common subtle bug in window function code. If you write SUM(amount) OVER (PARTITION BY region ORDER BY sale_date), you will get a running total, not a region total. The last row in each region will show the full region total, but every other row won't. Omit ORDER BY when you want the same aggregate value on every row in the partition.
You cannot reference a column alias for a window function in the same query's WHERE clause. The fix is always to wrap the query in a CTE or subquery. This is a SQL execution order issue, not a syntax quirk — the WHERE clause runs before window functions are evaluated.
By default, all NULL values in a PARTITION BY column are grouped together into a single partition. This can lead to surprising results if your partitioning column has NULLs. Always check for unexpected NULLs in your partition columns using techniques from Understanding SQL NULL Handling: COALESCE, NULLIF, and IS NULL for Reliable Data Queries.
PARTITION BY does not sort your results. The rows within each partition may appear in any order in the output unless you add an ORDER BY clause at the query level (outside the window function). Always use a final ORDER BY on the query itself if output order matters.
PARTITION BY is the mechanism that gives window functions their precision. It draws a logical boundary around subsets of your data so that each row's calculation only "sees" rows that share the same partition key. Unlike GROUP BY, it never reduces the number of rows — it just changes the scope of what the window function measures.
The key decisions you face when writing a partitioned window function:
PARTITION BY list.ORDER BY. If the value should accumulate or rank progressively, add it.PARTITION BY.From here, your natural next steps are:
ORDER BY inside a window function interacts with frame definitionsWindow functions are one of the highest-leverage tools in SQL. Mastering PARTITION BY is the foundation everything else is built on.