PARTITION BY: Practical Examples for Group Analytics

1. What PARTITION BY does and why rows are preserved

PARTITION BY is almost never used in isolation, but almost always in conjunction with at least one aggregate or window-only function whose computation basis it defines. Without such a function in the same SELECT clause, PARTITION BY has no discernible effect on the result, which is why it syntactically always sits inside an OVER clause and is never used alone.

PARTITION BY is the part of the OVER clause that splits a query's result set into logical subgroups, within which a window function computes independently of the others. The crucial difference from GROUP BY: PARTITION BY never collapses the rows of a group into a single result row. Every row stays individually visible, but additionally receives the value computed from its particular partition. This exact behavior is what makes PARTITION BY the tool for group analytics where both detail and context are needed.

A vivid example: a company wants to see, for every single order, how it compares to the average of its region. With GROUP BY you would only get one row per region with the average value, and the individual orders would be lost. With PARTITION BY region, every order stays as its own row, and the regional average sits next to it as an extra column. This property of PARTITION BY is the reason it is used so frequently in reporting queries, where detail tables need to be enriched with contextual information.

2. Syntax: PARTITION BY in detail

A common beginner mistake is trying to use PARTITION BY outside an OVER clause, for instance directly after FROM or as a substitute for GROUP BY. PARTITION BY exists exclusively inside the parentheses of OVER and has no meaning outside that context in standard SQL, which makes this mistake especially common among beginners.

The syntax of PARTITION BY is deliberately simple: inside the parentheses of OVER, the keyword PARTITION BY is followed by a comma-separated list of one or more columns. PARTITION BY always comes before an optional ORDER BY within the same OVER clause. If PARTITION BY is omitted entirely, the database treats the whole result set as a single partition, which makes sense for global calculations such as a grand average across all rows.

It is important to understand that PARTITION BY only affects the computation basis of the window function, not the output of the query itself. It filters out no rows and does not sort the output. Sorting the result rows remains the job of the final ORDER BY clause of the whole query, which exists independently of the PARTITION BY inside the OVER clause and serves a completely different purpose.

-- Basic PARTITION BY: regional average next to each order
SELECT
    order_id,
    region,
    order_amount,
    AVG(order_amount) OVER (PARTITION BY region) AS region_avg
FROM orders
ORDER BY region, order_amount DESC;

-- Result (excerpt)
-- order_id | region | order_amount | region_avg
-- 1042     | North  |         1250 |     980.50
-- 1055     | North  |          900 |     980.50
-- 1071     | South  |         1500 |    1120.00

Before choosing a partitioning column, it is worth briefly considering the cardinality of the column, that is, the number of distinct values. A column with only two or three distinct values creates very large partitions in which the window function has to compute over many thousands of rows. A column with very high cardinality, such as a unique ID, on the other hand, creates partitions with just a single row each, which makes the aggregation effectively meaningless. The most practically useful partitioning column usually lies somewhere in between, for instance a category, a region, or a calendar month.

3. Single-column partitioning: a simple example

The simplest form of PARTITION BY partitions by exactly one column, for instance customer segment, product category, or department. This form is the default case in most reporting queries and covers a large share of practical use cases. You typically combine PARTITION BY with an aggregate function like SUM, AVG or COUNT to compute a metric per group, which is then attached to every row of the group as context.

A common practical example is computing the share a single record contributes to the total of its group. With amount / SUM(amount) OVER (PARTITION BY category), you can compute the percentage share of category revenue per row, without needing a second query or a self-join. This kind of share calculation is one of the most common reasons to use PARTITION BY in the first place, because it is practically impossible to achieve in a single query with classic GROUP BY.

-- Single-column partitioning: share of each product within its category
SELECT
    category,
    product_name,
    revenue,
    ROUND(
        100.0 * revenue / SUM(revenue) OVER (PARTITION BY category),
        2
    ) AS pct_of_category_revenue
FROM product_revenue
ORDER BY category, revenue DESC;

4. Multi-column partitioning: multidimensional groups

Another reason to use multiple partitioning columns is the need to correctly represent hierarchical business structures. A corporation with several country subsidiaries, each of which operates several branches, needs partitioning by country and branch simultaneously for a branch-level analysis with country-wide context. Without this multi-column partitioning, you would have to either write two separate queries or work with nested subqueries, which considerably worsens the maintainability of the SQL code.

PARTITION BY is not limited to a single column. When multiple columns are listed comma-separated, for instance PARTITION BY region, product_category, a multidimensional grouping emerges: two rows only belong to the same partition if both column values match. This technique becomes necessary as soon as an analysis needs to group by more than one attribute at once, for example revenue per region and calendar year, to enable year-over-year comparisons within each region.

The order of columns in PARTITION BY has no effect on the result of the window function itself, unlike the column order in a GROUP BY clause with ROLLUP or CUBE, where the column order affects subtotals. With PARTITION BY, only the combination of column values determines which rows belong to the same group, regardless of whether region comes before or after product_category. This property makes multi-column partitioning predictable and easy to extend when another dimension needs to be added.

-- Multi-column PARTITION BY: revenue share within region AND category
SELECT
    region,
    product_category,
    product_name,
    revenue,
    SUM(revenue) OVER (
        PARTITION BY region, product_category
    ) AS category_total_in_region,
    ROUND(
        100.0 * revenue / SUM(revenue) OVER (PARTITION BY region, product_category),
        2
    ) AS pct_of_category
FROM product_revenue
ORDER BY region, product_category, revenue DESC;

5. PARTITION BY combined with ORDER BY

This combination also allows using several different sort criteria for different window functions within the same query, within the same partition. A query can simultaneously include ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) for a ranking by revenue and LAG(price) OVER (PARTITION BY category ORDER BY date) for a temporal price comparison, where both functions share the same partition but use different sort logic. This independence of PARTITION BY and ORDER BY per window function makes complex, multidimensional analyses possible in a single query.

The real strength of PARTITION BY only shows once it is combined with ORDER BY inside the same OVER clause. PARTITION BY defines the group, ORDER BY the order of the rows within that group. This combination is the foundation for all position-dependent calculations per group: running totals per customer, rankings per category, or accessing the previous row within the same region with LAG.

The order of the clauses matters: PARTITION BY always comes before ORDER BY inside the parentheses. PARTITION BY region ORDER BY order_date means: first form groups by region, then sort the rows within each region by date. The window function then operates exclusively within these sorted groups, and rows from a different region never flow into the computation of another region.

6. Running calculations per group without collapsing

This property is especially valuable in data migration and consolidation projects, where historical data from several source systems is merged together. A running checksum per source system, computed with PARTITION BY source_system, immediately surfaces inconsistencies, without needing a separate query for every source system.

One of the most practically valuable applications of PARTITION BY is running calculations within a group, without the detail rows collapsing along the way. A classic example is a running total per customer: SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) returns, for every order of a customer, the sum of all previous orders of that customer, starting at zero and growing with every additional row. As soon as a new customer starts, the running total automatically resets to zero, because PARTITION BY cleanly separates the groups from each other.

Without PARTITION BY, the same calculation would produce a global running total across all customers, which rarely makes business sense. The reset per group is not extra logic you have to program explicitly, it is an automatic consequence of PARTITION BY treating every group as an independent computation unit. This behavior saves a considerable amount of subquery and self-join code in practice compared to older SQL approaches.

Another practical example of running calculations per group is determining a row's position within its partition, combined with the group maximum. With MAX(amount) OVER (PARTITION BY category), every row can directly show how far its own value is from the best value in the category, without needing a second query. This kind of comparison to the group maximum or group minimum is a recurring pattern in quality control, price comparisons and performance evaluations, which PARTITION BY solves in a single, well readable query.

-- Running total per customer, resetting cleanly at each new partition
SELECT
    customer_id,
    order_date,
    order_amount,
    SUM(order_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS running_total_per_customer
FROM orders
ORDER BY customer_id, order_date;

7. PARTITION BY with different aggregate functions

This versatility makes PARTITION BY one of the most frequently used clauses in production analytics queries, far beyond plain sum and average calculations.

PARTITION BY can be combined with practically any aggregate function, not just SUM and AVG. COUNT(*) OVER (PARTITION BY status) returns the number of rows per status value, useful for workload analyses. MIN and MAX within a partition identify the earliest or latest value per group, for instance a customer's first order date, which can then be attached to every one of that customer's order rows as a reference value.

The window-only functions like ROW_NUMBER, RANK and LAG also benefit enormously from PARTITION BY, because they then apply their positional logic per group instead of globally. ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) numbers each customer's orders separately, starting at 1 for every new customer. This combination of PARTITION BY and a window-only function is one of the most frequently recurring patterns in production SQL queries for reporting and analytics.

8. Common mistakes with PARTITION BY

Combining with LAG and LEAD also benefits strongly from deliberately chosen partitioning, since both functions, without PARTITION BY, would read row by row across the entire unpartitioned result set, which can lead to business-wise incorrect comparisons between different groups when several groups appear in the same query.

The most common mistake when using PARTITION BY is confusing it with a WHERE filter. PARTITION BY restricts neither the result rows nor the input rows, it only changes which rows the window function computes over. Anyone who believes PARTITION BY region = 'North' would restrict the output to the North region is fundamentally misusing the clause, because PARTITION BY only accepts a column list, not a filter condition. Actually restricting the result set continues to happen exclusively via WHERE before the window function.

A second common mistake is missing ORDER BY when the window function is actually supposed to perform a position-dependent calculation. PARTITION BY alone only returns a constant value per group, such as the group average. If a running total or a rank is also needed, ORDER BY must be included as well, otherwise the database either returns an error or a result that does not match business expectations, because without ORDER BY there is no well defined order within the partition.

A third practical mistake arises when developers confuse PARTITION BY with the final ORDER BY clause of the query and assume PARTITION BY determines the order of the output rows. The two clauses have nothing to do with each other: PARTITION BY only affects which rows belong together for the window function computation, while sorting the output is controlled independently through its own, final ORDER BY at the end of the query. Anyone expecting sorted output has to specify that final ORDER BY explicitly, even when PARTITION BY and ORDER BY inside the OVER clause reference the same columns.

-- Common mistake: PARTITION BY is not a filter
-- WRONG: this is a syntax error, PARTITION BY only takes a column list
-- SELECT * FROM orders OVER (PARTITION BY region = 'North')

-- RIGHT: filter with WHERE, then partition for the calculation
SELECT
    order_id,
    region,
    order_amount,
    AVG(order_amount) OVER (PARTITION BY region) AS region_avg
FROM orders
WHERE region = 'North';

9. Performance considerations for large partitions

In database systems that support physically partitioned tables at the storage level, distinct from the logical PARTITION BY clause in the query, a physical table partitioning that matches the frequently used PARTITION BY columns can, in addition to the logical partitioning, bring performance benefits, because the database can exclude entire storage partitions before the actual window function computation even begins.

On very large tables with many distinct partition values, it pays off to have a composite index with the PARTITION BY columns first and the ORDER BY columns second. Such an index lets the optimizer read the data already sorted by partition, instead of performing a separate and potentially expensive sort step over the entire result set. Especially on tables with millions of rows and many small partitions, this difference can be substantial.

Another aspect: very large individual partitions, for instance when PARTITION BY groups by a column with few distinct values, concentrate the entire computational load on a small number of huge groups. In such cases, a finer, multi-column partitioning can distribute the work more evenly and thereby improve the parallelizability of the query in the execution plan, provided the database system supports parallel execution for window functions.

10. Summary

In summary, PARTITION BY is therefore less a standalone tool than the building block that turns a global window function computation into a grouped one, without giving up the fundamental benefit of window functions, namely preserving every detail row.

PARTITION BY splits a result set into logical groups without ever losing a single row. The difference from GROUP BY is fundamental: instead of one result row per group, every detail row is preserved and additionally gets a value computed from its partition. Single-column partitioning covers the majority of practical cases, multi-column partitioning enables multidimensional groupings, and combining it with ORDER BY is what makes position-dependent calculations like running totals and rankings per group possible in the first place.

In practice, PARTITION BY replaces numerous subqueries and self-joins that would otherwise be needed to unite detail and aggregate data in the same query. Anyone who knows the most common mistakes, confusing it with a filter and forgetting ORDER BY for position-dependent calculations, can use PARTITION BY confidently and productively for group analytics of any scale.

11. FAQ: PARTITION BY for group analytics