Imagine a university has a database containing students and their marks.
The university wants to perform different tasks:
We can perform these tasks using Window Functions in PostgreSQL.
Window functions allow us to perform calculations across a group of related rows while keeping individual rows in the result.
A Window Function performs a calculation across a set of related rows, called a window, without combining those rows into a single result.
In simple words:
A window function calculates information about related rows while keeping every individual record visible.
For example, suppose we have the following student records:
| Student | Department | Marks |
|---|---|---|
| Ali | CS | 85 |
| Sara | CS | 90 |
| Ahmed | IT | 75 |
| Hina | IT | 95 |
We want to display each student’s marks along with the average marks of their department.
A normal GROUP BY query returns one row per department. A window function can display all four students along with their department averages.
We will use a students table for practical examples.
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
department VARCHAR(50),
marks INT
);
INSERT INTO students
VALUES
(1, 'Ali', 'Computer Science', 85),
(2, 'Sara', 'Computer Science', 90),
(3, 'Ahmed', 'Information Technology', 75),
(4, 'Hina', 'Information Technology', 95),
(5, 'Usman', 'Mathematics', 80),
(6, 'Ayesha', 'Mathematics', 88),
(7, 'Bilal', 'Computer Science', 70),
(8, 'Zara', 'Information Technology', 85);
SELECT *
FROM students;
Before learning window functions, understand the difference between GROUP BY and OVER().
Calculate average marks department-wise.
SELECT
department,
AVG(marks) AS average_marks
FROM students
GROUP BY department;
Expected output:
| department | average_marks |
|---|---|
| Computer Science | 81.67 |
| Information Technology | 85 |
| Mathematics | 84 |
Notice that GROUP BY returns only three rows.
SELECT
student_name,
department,
marks,
AVG(marks) OVER (
PARTITION BY department
) AS department_average
FROM students;
Expected output:
| student_name | department | marks | department_average |
|---|---|---|---|
| Ali | Computer Science | 85 | 81.67 |
| Sara | Computer Science | 90 | 81.67 |
| Bilal | Computer Science | 70 | 81.67 |
| Ahmed | Information Technology | 75 | 85 |
| Hina | Information Technology | 95 | 85 |
| Zara | Information Technology | 85 | 85 |
| Usman | Mathematics | 80 | 84 |
| Ayesha | Mathematics | 88 | 84 |
The window function displays the average for each department while keeping all student records.
The OVER() clause defines the window of rows used by a window function.
SELECT
column_name,
window_function() OVER ()
FROM table_name;
Example:
SELECT
student_name,
marks,
AVG(marks) OVER () AS overall_average
FROM students;
This calculates the average marks of all students and displays the same average alongside every student.
| Component | Purpose |
|---|---|
OVER() |
Defines the window |
PARTITION BY |
Divides rows into groups |
ORDER BY |
Defines the order of rows |
ROWS |
Defines a specific row-based window frame |
We will learn these components step by step.
PARTITION BY divides records into separate groups for window calculations.
For example, calculate the average marks of each department.
SELECT
student_name,
department,
marks,
AVG(marks) OVER (
PARTITION BY department
) AS department_average
FROM students;
Understanding the query:
AVG(marks) calculates average marks.OVER() defines the window.PARTITION BY department divides students by department.Important: PARTITION BY does not remove individual rows. Every student remains in the result.
We can use ORDER BY inside OVER() to define the order in which rows are processed.
For example:
SELECT
student_name,
marks,
ROW_NUMBER() OVER (
ORDER BY marks DESC
) AS row_number
FROM students;
Expected output:
| student_name | marks | row_number |
|---|---|---|
| Hina | 95 | 1 |
| Sara | 90 | 2 |
| Ayesha | 88 | 3 |
| Ali | 85 | 4 |
| Zara | 85 | 5 |
| Usman | 80 | 6 |
| Ahmed | 75 | 7 |
| Bilal | 70 | 8 |
The highest marks receive row number 1.
Note: The query’s final output order is not guaranteed unless an outer ORDER BY is specified. For predictable results, use:
SELECT
student_name,
marks,
ROW_NUMBER() OVER (
ORDER BY marks DESC, student_id
) AS row_number
FROM students
ORDER BY marks DESC, student_id;
ROW_NUMBER() assigns a unique sequential number to each row within a window.
ROW_NUMBER() OVER (
ORDER BY column_name
)
SELECT
student_name,
department,
marks,
ROW_NUMBER() OVER (
ORDER BY marks DESC, student_id
) AS row_number
FROM students
ORDER BY marks DESC, student_id;
This assigns a unique number to every student based on marks.
Suppose we want to assign numbers separately within each department.
SELECT
student_name,
department,
marks,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY marks DESC, student_id
) AS row_number
FROM students
ORDER BY department, row_number;
Each department starts numbering from 1.
RANK() assigns a rank to each row based on the specified ordering.
If two or more rows have the same value, they receive the same rank. The next rank is skipped.
RANK() OVER (
ORDER BY column_name DESC
)
SELECT
student_name,
marks,
RANK() OVER (
ORDER BY marks DESC
) AS student_rank
FROM students
ORDER BY marks DESC;
Expected output:
| student_name | marks | student_rank |
|---|---|---|
| Hina | 95 | 1 |
| Sara | 90 | 2 |
| Ayesha | 88 | 3 |
| Ali | 85 | 4 |
| Zara | 85 | 4 |
| Usman | 80 | 6 |
| Ahmed | 75 | 7 |
| Bilal | 70 | 8 |
Notice that Ali and Zara both receive rank 4. The next rank is 6, not 5.
DENSE_RANK() also assigns ranks based on values.
However, unlike RANK(), it does not skip the next rank when two or more rows have the same value.
DENSE_RANK() OVER (
ORDER BY column_name DESC
)
SELECT
student_name,
marks,
DENSE_RANK() OVER (
ORDER BY marks DESC
) AS dense_rank
FROM students
ORDER BY marks DESC;
Expected output:
| student_name | marks | dense_rank |
|---|---|---|
| Hina | 95 | 1 |
| Sara | 90 | 2 |
| Ayesha | 88 | 3 |
| Ali | 85 | 4 |
| Zara | 85 | 4 |
| Usman | 80 | 5 |
| Ahmed | 75 | 6 |
| Bilal | 70 | 7 |
These three functions are commonly used for ranking and numbering records.
| Function | Same values receive same result? | Skips numbers? |
|---|---|---|
| ROW_NUMBER() | No | No |
| RANK() | Yes | Yes |
| DENSE_RANK() | Yes | No |
Suppose the marks are:
95, 90, 85, 85, 80
The results will be:
| Marks | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 95 | 1 | 1 | 1 |
| 90 | 2 | 2 | 2 |
| 85 | 3 | 3 | 3 |
| 85 | 4 | 3 | 3 |
| 80 | 5 | 5 | 4 |
Remember:
We have already learned aggregate functions such as:
We can also use these functions with OVER().
Count the number of students in each department.
SELECT
student_name,
department,
COUNT(*) OVER (
PARTITION BY department
) AS total_students
FROM students;
Calculate the total marks of each department.
SELECT
student_name,
department,
marks,
SUM(marks) OVER (
PARTITION BY department
) AS department_total_marks
FROM students;
Calculate the average marks of each department.
SELECT
student_name,
department,
marks,
AVG(marks) OVER (
PARTITION BY department
) AS average_marks
FROM students;
Display the highest marks in each department.
SELECT
student_name,
department,
marks,
MAX(marks) OVER (
PARTITION BY department
) AS highest_marks
FROM students;
Display the lowest marks in each department.
SELECT
student_name,
department,
marks,
MIN(marks) OVER (
PARTITION BY department
) AS lowest_marks
FROM students;
A running total is a continuously updated total calculated row by row.
For example, suppose we want to calculate the running total of student marks.
SELECT
student_name,
marks,
SUM(marks) OVER (
ORDER BY student_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS running_total
FROM students
ORDER BY student_id;
Expected output:
| student_name | marks | running_total |
|---|---|---|
| Ali | 85 | 85 |
| Sara | 90 | 175 |
| Ahmed | 75 | 250 |
| Hina | 95 | 345 |
| Usman | 80 | 425 |
| Ayesha | 88 | 513 |
| Bilal | 70 | 583 |
| Zara | 85 | 668 |
Understanding the query:
ORDER BY student_id defines the calculation order.UNBOUNDED PRECEDING starts from the first row.CURRENT ROW ends at the current row.SUM(marks) calculates the running total.The ROWS BETWEEN clause defines which rows are included in a window calculation.
Common options:
| Option | Meaning |
|---|---|
UNBOUNDED PRECEDING |
Start from the first row |
CURRENT ROW |
Include the current row |
UNBOUNDED FOLLOWING |
Continue to the last row |
1 PRECEDING |
Include one previous row |
1 FOLLOWING |
Include one next row |
SELECT
student_name,
marks,
SUM(marks) OVER (
ORDER BY student_id
ROWS BETWEEN 1 PRECEDING
AND CURRENT ROW
) AS recent_total
FROM students
ORDER BY student_id;
For each student, this calculates the sum of their marks and the previous student’s marks, except for the first student who has no previous row.
LAG() retrieves a value from a previous row.
For example, we want to compare each student’s marks with the previous student’s marks.
LAG(column_name) OVER (
ORDER BY column_name
)
SELECT
student_name,
marks,
LAG(marks) OVER (
ORDER BY student_id
) AS previous_marks
FROM students
ORDER BY student_id;
Expected output:
| student_name | marks | previous_marks |
|---|---|---|
| Ali | 85 | NULL |
| Sara | 90 | 85 |
| Ahmed | 75 | 90 |
| Hina | 95 | 75 |
| Usman | 80 | 95 |
| Ayesha | 88 | 80 |
| Bilal | 70 | 88 |
| Zara | 85 | 70 |
The first student has no previous row, so PostgreSQL returns NULL.
LEAD() retrieves a value from a following row.
For example:
SELECT
student_name,
marks,
LEAD(marks) OVER (
ORDER BY student_id
) AS next_student_marks
FROM students
ORDER BY student_id;
Expected output:
| student_name | marks | next_student_marks |
|---|---|---|
| Ali | 85 | 90 |
| Sara | 90 | 75 |
| Ahmed | 75 | 95 |
| Hina | 95 | 80 |
| Usman | 80 | 88 |
| Ayesha | 88 | 70 |
| Bilal | 70 | 85 |
| Zara | 85 | NULL |
The last student has no following row, so PostgreSQL returns NULL.
| LAG() | LEAD() |
|---|---|
| Retrieves a previous row’s value | Retrieves a following row’s value |
| Looks backward | Looks forward |
FIRST_VALUE() returns the first value in the defined window frame.
Example: Display the highest marks in each department alongside every student.
SELECT
student_name,
department,
marks,
FIRST_VALUE(marks) OVER (
PARTITION BY department
ORDER BY marks DESC
) AS highest_marks
FROM students
ORDER BY department, marks DESC;
Because the rows are ordered by marks in descending order, the first value is the highest mark in each department.
LAST_VALUE() returns the last value in the defined window frame.
Example:
SELECT
student_name,
department,
marks,
LAST_VALUE(marks) OVER (
PARTITION BY department
ORDER BY marks DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS lowest_marks
FROM students
ORDER BY department, marks DESC;
The complete window frame is specified so that LAST_VALUE returns the last mark in the entire department partition.
Suppose we want to rank students separately within each department.
SELECT
student_name,
department,
marks,
RANK() OVER (
PARTITION BY department
ORDER BY marks DESC
) AS department_rank
FROM students
ORDER BY department, department_rank;
Expected output:
| student_name | department | marks | department_rank |
|---|---|---|---|
| Sara | Computer Science | 90 | 1 |
| Ali | Computer Science | 85 | 2 |
| Bilal | Computer Science | 70 | 3 |
| Hina | Information Technology | 95 | 1 |
| Zara | Information Technology | 85 | 2 |
| Ahmed | Information Technology | 75 | 3 |
| Ayesha | Mathematics | 88 | 1 |
| Usman | Mathematics | 80 | 2 |
Each department starts its ranking from 1.
PostgreSQL allows us to define a named window using the WINDOW clause.
This is useful when multiple window functions use the same window definition.
SELECT
student_name,
department,
marks,
AVG(marks) OVER department_window AS average_marks,
RANK() OVER department_window AS department_rank
FROM students
WINDOW department_window AS (
PARTITION BY department
ORDER BY marks DESC
);
Here, department_window is a reusable window definition.
Both window functions use the same partitioning and ordering.
| Window Functions | GROUP BY |
|---|---|
| Keeps individual rows in the result | Combines rows into groups |
Uses OVER() |
Uses GROUP BY |
| Supports ranking and row comparisons | Commonly used for aggregate calculations |
| Can calculate running totals | Does not directly calculate row-by-row running totals |
Can use PARTITION BY |
Groups by specified columns |
OVER() clause.PARTITION BY divides rows into groups.ORDER BY defines the order of rows in a window.ROW_NUMBER() assigns unique row numbers.RANK() assigns ranks and skips numbers after ties.DENSE_RANK() assigns ranks without skipping numbers.SUM() OVER() can calculate running totals.AVG() OVER() calculates averages while keeping individual rows.LAG() retrieves values from previous rows.LEAD() retrieves values from following rows.FIRST_VALUE() returns the first value in a window frame.LAST_VALUE() returns the last value in a window frame.AVG() OVER().COUNT() OVER().RANK() and DENSE_RANK() to compare student rankings.ROW_NUMBER().RANK().PARTITION BY and ORDER BY.WINDOW clause.OVER() clause?| Function / Keyword | Purpose |
|---|---|
OVER() |
Defines the window |
PARTITION BY |
Divides rows into groups |
ORDER BY |
Defines row order |
ROW_NUMBER() |
Assigns unique row numbers |
RANK() |
Assigns ranks with gaps |
DENSE_RANK() |
Assigns ranks without gaps |
COUNT() OVER() |
Counts rows in a window |
SUM() OVER() |
Calculates a sum over a window |
AVG() OVER() |
Calculates an average over a window |
LAG() |
Retrieves a previous row’s value |
LEAD() |
Retrieves a following row’s value |
FIRST_VALUE() |
Returns the first value |
LAST_VALUE() |
Returns the last value |
ROWS BETWEEN |
Defines a row-based window frame |
WINDOW |
Defines a reusable named window |
Next Topic: PostgreSQL Date and Time Functions
Students will learn how to work with dates, times, timestamps, extract years and months, calculate date differences, and format date and time values using PostgreSQL.