Practical window function patterns in PostgreSQL - Step-by-Step Execution

Concept Flow - Practical window function patterns

Start with table data

↓

Define window function

↓

Partition data (optional)

↓

Order data within partition

↓

Calculate window function value

↓

Return rows with window values

↓

End

Window functions process rows with optional partitioning and ordering, returning values alongside original rows.

Execution Sample

PostgreSQL

SELECT emp_id, dept, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank
FROM employees;

Ranks employees by salary within each department.

Execution Table

Step	emp_id	dept	salary	Partition (dept)	Order in Partition	RANK() Result
1	101	Sales	7000	Sales	1	1
2	102	Sales	6000	Sales	2	2
3	103	Sales	6000	Sales	3	2
4	104	HR	8000	HR	1	1
5	105	HR	7500	HR	2	2
6	106	HR	7000	HR	3	3
7	107	IT	9000	IT	1	1
8	108	IT	8500	IT	2	2
Exit						All rows processed, ranks assigned

💡 All rows processed, ranks assigned

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	After 7	After 8	Final
emp_id		101	102	103	104	105	106	107	108
dept		Sales	Sales	Sales	HR	HR	HR	IT	IT
salary		7000	6000	6000	8000	7500	7000	9000	8500
RANK()		1	2	2	1	2	3	1	2

Key Moments - 3 Insights

Why do employees with the same salary get the same rank?

Because RANK() assigns the same rank to ties, as shown in rows 2 and 3 where salary 6000 gets rank 2 for both.

What happens when the partition changes?

The ranking restarts at 1 for each new department partition, as seen when moving from Sales to HR at row 4.

Why is ordering important inside the partition?

Ordering determines the rank order; without ORDER BY salary DESC, ranks would not reflect salary order.

Visual Quiz - 3 Questions

Look at the execution table, what is the rank of emp_id 105 at step 5?

A 3

B 1

C 2

D 4

💡 Hint

Check the RANK() Result column at step 5 for emp_id 105.

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

A Step 4

B Step 5

C Step 3

D Step 6

💡 Hint

Look at the Partition (dept) column to see when it changes.

If we remove ORDER BY salary DESC, what would happen to the ranks?

A Ranks would all be 1

B Ranks would be assigned randomly within partitions

C Ranks would be assigned in insertion order

D Ranks would be NULL

💡 Hint

Ordering defines how ranks are assigned; without it, order is not guaranteed.

Concept Snapshot

Window functions compute values across rows related to the current row.
Syntax: function() OVER (PARTITION BY col ORDER BY col)
Partition divides data groups; order defines sequence.
Common functions: RANK(), ROW_NUMBER(), SUM(), AVG().
Results appear alongside original rows without grouping.

Full Transcript

This lesson shows how window functions work in PostgreSQL. We start with a table of employees with departments and salaries. The RANK() function assigns ranks to employees within each department based on salary order. The process partitions data by department, orders salaries descending, then calculates ranks. Equal salaries get the same rank, and ranks restart for each department. The execution table traces each row's rank assignment step-by-step. Key points include understanding partition changes, tie handling, and the importance of ordering. The quiz tests understanding of ranks at specific steps, partition changes, and effects of removing ordering. Window functions let you analyze data across rows without losing row details.