OVER clause with PARTITION BY in SQL - Step-by-Step Execution

Concept Flow - OVER clause with PARTITION BY

Start with full table

↓

Apply PARTITION BY to split rows into groups

↓

Within each group, apply window function

↓

Combine results preserving original row order

↓

Return result set with window function values per row

The OVER clause with PARTITION BY splits the table into groups and applies a window function to each group separately, returning results alongside original rows.

Execution Sample

SQL

SELECT department, employee, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

This query ranks employees by salary within each department.

Execution Table

Step	Row (department, employee, salary)	Partition	Order in Partition	RANK() Result
1	(Sales, Alice, 70000)	Sales	1	1
2	(Sales, Bob, 60000)	Sales	2	2
3	(Sales, Carol, 60000)	Sales	2	2
4	(HR, Dave, 65000)	HR	1	1
5	(HR, Eve, 55000)	HR	2	2
6	(IT, Frank, 80000)	IT	1	1
7	(IT, Grace, 75000)	IT	2	2
8	(IT, Heidi, 75000)	IT	2	2
9	End of rows	-	-	-

💡 All rows processed; ranks assigned within each department partition.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	After 7	After 8	Final
Current Row	None	(Sales, Alice, 70000)	(Sales, Bob, 60000)	(Sales, Carol, 60000)	(HR, Dave, 65000)	(HR, Eve, 55000)	(IT, Frank, 80000)	(IT, Grace, 75000)	(IT, Heidi, 75000)	None
Partition	None	Sales	Sales	Sales	HR	HR	IT	IT	IT	None
Rank	None	1	2	2	1	2	1	2	2	None

Key Moments - 3 Insights

Why do Bob and Carol have the same rank 2 in the Sales department?

Because they have the same salary and the RANK() function assigns the same rank to ties within the partition, as shown in execution_table rows 2 and 3.

Does the OVER clause reorder the entire table?

No, the OVER clause with PARTITION BY applies the window function within each group but preserves the original row order in the final result, as seen in the execution_table.

What happens if PARTITION BY is omitted?

The window function applies to the entire table as a single group, not partitioned, so ranks would be assigned across all rows together.

Visual Quiz - 3 Questions

Look at the execution_table, what is the rank of Heidi at step 8?

A 2

B 1

C 3

D None

💡 Hint

Check the 'RANK() Result' column at step 8 in the execution_table.

At which step does the partition change from Sales to HR?

A Step 3

B Step 4

C Step 5

D Step 6

💡 Hint

Look at the 'Partition' column in the variable_tracker to see when it changes.

If the ORDER BY salary DESC is changed to ASC, what happens to Alice's rank?

A It becomes 1

B It stays 1

C It becomes the highest rank number in Sales

D It becomes 2

💡 Hint

Ranks depend on ordering; changing DESC to ASC reverses rank order within partitions.

Concept Snapshot

OVER clause with PARTITION BY:
- Splits rows into groups by PARTITION BY columns
- Applies window function (e.g., RANK()) within each group
- Preserves original row order
- Useful for ranking, running totals, etc.
- Syntax: FUNCTION() OVER (PARTITION BY col ORDER BY col2)

Full Transcript

The OVER clause with PARTITION BY divides the table into groups based on specified columns. Then, a window function like RANK() is applied within each group separately. This means each group is ranked or aggregated independently, but the original row order is kept in the output. For example, ranking employees by salary within each department assigns ranks starting at 1 for the highest salary in each department. Ties get the same rank. The execution table shows each row processed, its partition group, order within that group, and the rank assigned. Variable tracking shows how the current row, partition, and rank change step by step. Key moments clarify why ties share ranks and that the table is not reordered globally. The quiz tests understanding of ranks, partition changes, and effects of ordering direction. This concept is essential for advanced SQL queries that analyze data in groups without collapsing rows.

Practice

1. What does the PARTITION BY clause do when used with the OVER clause in SQL?

easy

A. It divides the result set into groups to perform calculations within each group.

B. It filters rows before aggregation happens.

C. It sorts the entire result set globally.

D. It removes duplicate rows from the result.

Solution

1. Step 1: Understand the role of PARTITION BY

   The PARTITION BY clause groups rows based on column values, similar to grouping in real life like sorting students by class.

2. Step 2: See how OVER uses PARTITION BY

   The OVER clause applies functions like SUM or ROW_NUMBER within each group separately, not on the whole table.

3. Final Answer:

   It divides the result set into groups to perform calculations within each group. -> Option A

4. Quick Check:

   PARTITION BY = group rows for calculations [OK]

Hint: Think of PARTITION BY as grouping rows inside a window function [OK]

Common Mistakes:

• Confusing PARTITION BY with WHERE filtering
• Thinking PARTITION BY sorts all rows globally
• Assuming PARTITION BY removes duplicates

2. Which of the following is the correct syntax to calculate a running total of sales per department using OVER with PARTITION BY?

easy

A. SELECT department, sales, SUM(sales) OVER ORDER BY date PARTITION BY department AS running_total FROM sales_data;

B. SELECT department, sales, SUM(sales) OVER (PARTITION BY department ORDER BY date) AS running_total FROM sales_data;

C. SELECT department, sales, SUM(sales) PARTITION BY department OVER ORDER BY date AS running_total FROM sales_data;

D. SELECT department, sales, SUM(sales) OVER (ORDER BY date PARTITION BY department) AS running_total FROM sales_data;

Solution

1. Step 1: Identify correct OVER clause order

   The correct syntax places PARTITION BY before ORDER BY inside the parentheses after OVER.

2. Step 2: Check each option's syntax

   SELECT department, sales, SUM(sales) OVER (PARTITION BY department ORDER BY date) AS running_total FROM sales_data; correctly uses SUM(sales) OVER (PARTITION BY department ORDER BY date). Others misplace PARTITION BY or OVER keywords.

3. Final Answer:

   SELECT department, sales, SUM(sales) OVER (PARTITION BY department ORDER BY date) AS running_total FROM sales_data; -> Option B

4. Quick Check:

   PARTITION BY before ORDER BY inside OVER [OK]

Hint: Remember: OVER (PARTITION BY ... ORDER BY ...) is the correct order [OK]

Common Mistakes:

• Swapping PARTITION BY and ORDER BY order
• Placing PARTITION BY outside OVER parentheses
• Using PARTITION BY without OVER

3. Given the table Employees with columns Department and Salary, what will the following query return?

SELECT Department, Salary, RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS SalaryRank FROM Employees;

medium

A. Returns the salary and department without any ranking.

B. Ranks salaries across all employees ignoring departments.

C. Calculates the total salary per department.

D. Ranks salaries within each department from highest to lowest, starting at 1 for the highest salary.

Solution

1. Step 1: Understand RANK() with PARTITION BY

   The RANK() function assigns ranks starting at 1 within each partition (department), ordered by salary descending.

2. Step 2: Analyze the query output

   Each employee gets a rank based on salary within their department, highest salary ranked 1, ties get same rank.

3. Final Answer:

   Ranks salaries within each department from highest to lowest, starting at 1 for the highest salary. -> Option D

4. Quick Check:

   RANK() OVER PARTITION BY groups and ranks rows [OK]

Hint: RANK() with PARTITION BY ranks within groups, not whole table [OK]

Common Mistakes:

• Thinking RANK() ignores PARTITION BY
• Confusing RANK() with SUM()
• Assuming ranking is global, not per group

4. Identify the error in this SQL query:

SELECT employee_id, department, SUM(salary) OVER PARTITION BY department FROM employees;

medium

A. department column cannot be used in PARTITION BY.

B. SUM() cannot be used with OVER clause.

C. Missing parentheses around PARTITION BY clause inside OVER.

D. employee_id must be included in PARTITION BY.

Solution

1. Step 1: Check OVER clause syntax

   The OVER clause must have parentheses enclosing PARTITION BY and other clauses.

2. Step 2: Identify missing parentheses

   The query misses parentheses after OVER, it should be OVER (PARTITION BY department).

3. Final Answer:

   Missing parentheses around PARTITION BY clause inside OVER. -> Option C

4. Quick Check:

   OVER requires parentheses around PARTITION BY [OK]

Hint: Always put parentheses after OVER, even if only PARTITION BY used [OK]

Common Mistakes:

• Omitting parentheses after OVER
• Using aggregate without OVER for window function
• Including wrong columns in PARTITION BY

5. You want to find each student's score rank within their class and also show the average score of their class. Which SQL query correctly uses OVER with PARTITION BY to achieve this?

hard

A. SELECT student_id, class, score, RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class, AVG(score) OVER (PARTITION BY class) AS avg_class_score FROM student_scores;

B. SELECT student_id, class, score, RANK() OVER (ORDER BY score DESC PARTITION BY class) AS rank_in_class, AVG(score) OVER (PARTITION BY class) AS avg_class_score FROM student_scores;

C. SELECT student_id, class, score, RANK() PARTITION BY class ORDER BY score DESC AS rank_in_class, AVG(score) OVER (class) AS avg_class_score FROM student_scores;

D. SELECT student_id, class, score, RANK() OVER (PARTITION BY class ORDER BY score) AS rank_in_class, AVG(score) OVER (PARTITION BY class ORDER BY score) AS avg_class_score FROM student_scores;

Solution

1. Step 1: Use correct syntax for RANK() and AVG() with PARTITION BY

   Both functions must have OVER (PARTITION BY class ...). RANK() orders descending for highest score first; AVG() just partitions by class.

2. Step 2: Check each option's correctness

   SELECT student_id, class, score, RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class, AVG(score) OVER (PARTITION BY class) AS avg_class_score FROM student_scores; correctly uses RANK() OVER (PARTITION BY class ORDER BY score DESC) and AVG(score) OVER (PARTITION BY class). Others have syntax errors or wrong order.

3. Final Answer:

   SELECT student_id, class, score, RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class, AVG(score) OVER (PARTITION BY class) AS avg_class_score FROM student_scores; -> Option A

4. Quick Check:

   Use OVER (PARTITION BY ...) with correct order and parentheses [OK]

Hint: Use separate OVER clauses with PARTITION BY for each window function [OK]

Common Mistakes:

• Mixing ORDER BY and PARTITION BY order inside OVER
• Missing parentheses around PARTITION BY
• Using PARTITION BY without OVER