Pathubs SQL Curriculum • Module 15

SQL PARTITION BY Window Grouping

Master group-level window analytics in SQL: understand how PARTITION BY divides datasets into independent subsets without collapsing rows, compare it deeply against GROUP BY, combine it with aggregate and ranking functions, and perform multi-column partitioning.

⏱️ Estimated Time:30 Minutes

🎯 Level:Intermediate Window Analytics

📊 Track:Data Analytics & Reporting Engineering

✨ Mode:Live Partition Lab

Introduction: Group Metrics Without Collapsing Rows

Suppose you have the following employee compensation data:

name	department	salary
Amit	Sales	$80,000
Rahul	Sales	$70,000
Priya	HR	$75,000
Neha	HR	$65,000

How can you calculate the average salary for each department, or rank employees within each department, while still keeping every individual employee row intact in your output?

Standard GROUP BY cannot do this because it collapses rows into 2 summary lines. The answer is PARTITION BY.

What Is PARTITION BY? (The Core Mental Model)

Core Mental Model

PARTITION BY divides input rows into separate logical buckets. The window function then evaluates independently inside each bucket without collapsing the original rows.

Diagram 1: Logical Partition Separation

All 4 Input Rows
employees table

── PARTITION BY department ──▶

Partition 1: Sales (2 rows)
Partition 2: HR (2 rows)

── Window Function Evaluates ──▶

Output
All 4 Rows Preserved + Group Metric

Basic Syntax & Structure

PARTITION BY is placed inside the OVER() clause of any SQL window function:

SELECT
    column_1,
    column_2,
    function_name() OVER (
        PARTITION BY grouping_column
        ORDER BY sorting_column
    ) AS computed_metric
FROM table_name;

PARTITION BY With ROW_NUMBER()

Numbering restarts at 1 for each distinct department partition:

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS dept_row_num
FROM employees;

name	department	salary	dept_row_num
Amit	Sales	$80,000	1
Rahul	Sales	$70,000	2
Priya	HR	$75,000	1 (Restarted!)
Neha	HR	$65,000	2

PARTITION BY Without ORDER BY

PARTITION BY does not require an ORDER BY clause when calculating non-order-dependent aggregates (like AVG or SUM):

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (
        PARTITION BY department
    ) AS department_avg
FROM employees;

PARTITION BY With Aggregate Functions (SUM, AVG, COUNT, MIN, MAX)

This is one of the most powerful patterns in SQL analytics: displaying the overall group total or average directly beside individual granular rows:

SELECT
    name,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total_payroll,
    COUNT(*) OVER (PARTITION BY department) AS dept_headcount,
    MAX(salary) OVER (PARTITION BY department) AS dept_highest_salary
FROM employees;

Major Conceptual Comparison: PARTITION BY vs. GROUP BY

Solving the same analytical question with both approaches illustrates the core difference:

GROUP BY (Collapses Rows):
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department;

-- Output: Exactly 2 rows (Sales: 75k, HR: 70k). Individual employee names are lost!

PARTITION BY (Keeps All Rows):
SELECT name, department, salary,
AVG(salary) OVER (PARTITION BY department) AS avg_sal
FROM employees;

-- Output: All 4 rows preserved! Each employee sees their own salary + department average.

Diagram 2: Row Count Impact: GROUP BY vs PARTITION BY

GROUP BY

4 Rows ➔ 2 Rows

Collapses records into summary rows

PARTITION BY

4 Rows ➔ 4 Rows

Preserves granular rows + appends stats

Combining PARTITION BY With ORDER BY

When combined, PARTITION BY defines which group, while ORDER BY defines how rows are processed and sorted inside that group:

Sales Partition ➔ Sort by Salary DESC ➔ Assign Ranks 1, 2, 3
HR Partition ➔ Sort by Salary DESC ➔ Assign Ranks 1, 2, 3

Multiple Columns in PARTITION BY: Multi-Dimensional Grouping

You can partition by multiple columns simultaneously (e.g. PARTITION BY department, role):

SELECT
    name,
    department,
    role,
    salary,
    AVG(salary) OVER (
        PARTITION BY department, role
    ) AS dept_role_avg_salary
FROM employees;

Real-World Data Analytics Use Cases

Salary Benchmarking: Comparing employee compensation against their department average.
Regional Sales Contribution: Calculating a salesperson's percentage share of regional revenue.
Customer Lifetime Order Metrics: Calculating average order value per customer across transactional rows.
Category Leaderboards: Ranking top-selling products independently within each e-commerce category.

Reference Matrix Across Window Functions

Function Syntax	Role of PARTITION BY	Requires ORDER BY?
`ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sal DESC)`	Restarts row numbers at 1 per department	Yes (Mandatory for deterministic numbering)
`DENSE_RANK() OVER (PARTITION BY dept ORDER BY sal DESC)`	Restarts dense ranking at 1 per department	Yes (Mandatory for ranking)
`AVG(sal) OVER (PARTITION BY dept)`	Calculates department average alongside each row	No (Calculates across entire partition)
`SUM(sal) OVER (PARTITION BY dept)`	Calculates total department payroll	No (Unless running totals are desired)

Logical Window Partitioning vs. Physical Database Partitioning

Important Terminology Clarification

SQL window PARTITION BY is a query-scoped logical grouping mechanism. It is completely distinct from physical database table partitioning (splitting table data across disk partitions for storage performance).

Common PARTITION BY Mistakes to Avoid

1. Confusing with GROUP BY

Expecting PARTITION BY to collapse rows into a single summary record.

2. Assuming PARTITION BY Auto-Sorts

Omitting ORDER BY when ranking. PARTITION BY only groups; it does not sort.

3. Direct WHERE Filtering

Attempting to filter window partitions directly in WHERE (e.g. WHERE ROW_NUMBER() = 1). Use a CTE instead.

4. Over-Partitioning

Partitioning by unique keys (like primary ID), which creates 1-row partitions where all ranks are 1.

Practical PARTITION BY Exercises

Exercise 1: Salary Difference from Department Average

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_dept_avg
FROM employees;

Exercise 2: Top 2 Earners per Department Using CTE

WITH ranked_employees AS (
    SELECT
        name, department, salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rnk
    FROM employees
)
SELECT * FROM ranked_employees
WHERE rnk <= 2;

⚡ Live Interactive SQL PARTITION BY Practice Lab

Test logical partition groupings, compare GROUP BY vs PARTITION BY row counts, and explore multi-column partitions

Function:

Partition By:

Name	Department	Role	Salary	Computed Result
Amit Verma	Sales	Manager	$90,000	Partition Avg: $78,333
Rahul Sharma	Sales	Manager	$85,000	Partition Avg: $78,333
Rohan Gupta	Sales	Analyst	$60,000	Partition Avg: $78,333
Priya Patel	HR	Manager	$80,000	Partition Avg: $69,000
Neha Singh	HR	Analyst	$65,000	Partition Avg: $69,000
Vikram Joshi	HR	Analyst	$62,000	Partition Avg: $69,000

SQL PARTITION BY Best Practices

Use PARTITION BY for Metric Enrichment: When you need group totals, averages, or ranks beside detailed rows.
Use GROUP BY for Pure Aggregation: When you want to collapse granular rows into a concise summary table.
Include ORDER BY When Ranking: Always provide explicit ordering when using ROW_NUMBER() or DENSE_RANK().
Use Multi-Column Partitions Thoughtfully: Only combine partition columns when your business logic genuinely requires intersectional grouping.

What You Should Know Now: Checklist

✓PARTITION BY Concept: Creates logical subsets where window functions evaluate independently without collapsing rows.
✓PARTITION BY vs GROUP BY: PARTITION BY preserves all rows; GROUP BY collapses rows.
✓With Ranking Functions: Restarts ranking sequences at 1 for each partition.
✓With Aggregate Functions: Computes group statistics (SUM, AVG, COUNT) beside individual records.

TEST YOUR KNOWLEDGE

Knowledge Assessment: SQL PARTITION BY

Test your understanding of logical partitioning, PARTITION BY vs GROUP BY differences, aggregate window functions, and multi-column partitions.

Question 1 of 8Current Score: 0 / 0

What is the fundamental role of PARTITION BY inside a window function?