12.1 Window Functions Overview (OVER, PARTITION BY, ORDER BY)

Level: 12 | Version: v1.1 | Author: Meptrasoft

Overview

A window function performs a calculation across related rows while keeping every original row in the result.

This is the key difference from GROUP BY:

GROUP BY
→ Combines rows
→ Fewer result rows

WINDOW FUNCTION
→ Calculates across rows
→ Keeps every result row

Window Function → Calculate across rows without collapsing them.

GROUP BY vs Window Functions — Preserving Rows vs Collapsing Summaries — Figure 1: GROUP BY collapses rows into summaries, while window functions calculate across related rows without removing the individual rows.

The OVER Clause

The OVER clause defines the window of rows used for the calculation.

It can include:

Part	Purpose
`PARTITION BY`	Divides rows into groups
`ORDER BY`	Defines the order within each group

Basic structure:

function_name()
OVER (
    PARTITION BY column
    ORDER BY column
)

OVER → Defines which rows the window function works across.

PARTITION BY

PARTITION BY divides rows into groups without collapsing them.

For example:

AVG(salary) OVER (
    PARTITION BY department
)

This calculates the average salary separately for each department while keeping every employee row.

Conceptually:

Finance
→ Finance average

Sales
→ Sales average

ORDER BY

Inside a window function, ORDER BY defines the order in which rows are considered.

For example:

SUM(salary) OVER (
    ORDER BY salary
)

The ordering is important for calculations such as running totals and rankings.

PARTITION BY → Which group? ORDER BY → In what order?

Simple PostgreSQL Example

Practice table:

CREATE TABLE employee (
    name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10,2)
);

INSERT INTO employee (name, department, salary)
VALUES
('Andrew', 'Finance', 50000),
('Brian', 'Finance', 50000),
('Charles', 'Finance', 20000),
('Daniel', 'Sales', 30000),
('Ethan', 'Sales', 20000);

Now calculate the average salary for each department:

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

Result idea:

name	department	salary	department_avg
Andrew	Finance	50000	40000
Brian	Finance	50000	40000
Charles	Finance	20000	40000
Daniel	Sales	30000	25000
Ethan	Sales	20000	25000

Notice that all five employees remain in the result.

GROUP BY vs Window Function

GROUP BY

SELECT department, AVG(salary)
FROM employee
GROUP BY department;

Result:

Finance → 40000
Sales   → 25000

Only one row is returned for each department.

Window Function

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department)
FROM employee;

Every employee remains visible, with the department average added.

GROUP BY → Summary rows Window Function → Original rows + calculated value

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
Window functions remove rows like GROUP BY	Window functions preserve the rows
`PARTITION BY` creates a permanent table	It only defines groups for the calculation
`ORDER BY` inside OVER sorts the final result automatically	It defines the window calculation order; final output ordering is a separate concern
Every window function needs `PARTITION BY`	`PARTITION BY` is optional
Every window function needs `ORDER BY`	`ORDER BY` is optional for some window calculations

Placement Quick Points

OVER
→ DEFINES WINDOW

PARTITION BY
→ DIVIDE INTO GROUPS

ORDER BY
→ DEFINE ORDER

WINDOW FUNCTION
→ KEEP ROWS

Window functions calculate across related rows.
They preserve individual result rows.
OVER defines the window.
PARTITION BY divides rows into groups without collapsing them.
ORDER BY controls the order within the window.
Window functions are useful for rankings, running totals, comparisons, and other row-by-row analytics.

Interview Questions

What is a window function?

A window function performs a calculation across related rows while keeping the individual rows in the result.

What is the purpose of OVER?

OVER defines the rows used by the window calculation.

What does PARTITION BY do?

It divides rows into groups for the window calculation without collapsing those rows.

What does ORDER BY do inside OVER?

It defines the order of rows within the window.

What is the main difference between GROUP BY and a window function?

GROUP BY → COLLAPSES ROWS

WINDOW FUNCTION → PRESERVES ROWS

Is PARTITION BY mandatory?

No. It is optional and depends on the calculation.

Practice & Hands-On Exercises

Using the employee table:

Calculate the average salary for each department using PARTITION BY:

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

Display each employee along with their department average.
Explain why the window-function query returns all employees.
Compare the result with a GROUP BY query.
Try ORDER BY salary inside OVER and observe the effect:

SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS running_total
FROM employee;

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

OVER
 ↓
PARTITION BY → GROUP
 ↓
ORDER BY → ORDER
 ↓
CALCULATE
 ↓
KEEP ALL ROWS

Window functions perform calculations across related rows without collapsing the result, with OVER, PARTITION BY, and ORDER BY defining the calculation window.

End of Window Functions Overview (OVER, PARTITION BY, ORDER BY)