Window Anatomy

To use any window function, you must define a Window—the set of rows the function will operate on. This is done using the OVER() clause.

Definition: The OVER() Clause

The OVER() clause defines the “window” of rows for a function. It consists of three optional components:

1. PARTITION BY: Divides the data into buckets (similar to GROUP BY).
2. ORDER BY: Defines the sequence of rows within each bucket.
3. FRAME: Defines the boundaries of the window (e.g., “the previous 3 rows”).

Context: Aggregation vs. Windowing

Standard Aggregate (GROUP BY)	Window Function (OVER)
Collapses many rows into 1 row.	Keeps all original rows.
You lose access to non-grouped columns.	You retain access to every column.
Result set size = number of groups.	Result set size = original table size.

Detailed Explanation: The Components

1. PARTITION BY

If you want a calculation to “reset” for every department, you partition by department. OVER(PARTITION BY Department)

2. ORDER BY

Within the window, how should the rows be ordered? This is critical for functions like RANK() or for calculating running totals. OVER(PARTITION BY Dept ORDER BY Salary DESC)

3. Empty OVER()

If you provide an empty OVER(), the “window” is the entire result set. SELECT Salary, SUM(Salary) OVER() FROM Employees; This would show each employee’s salary next to the total payroll of the whole company.

Example: Comparison to Average

We have an Employees table:

Name	Dept	Salary
Alice	IT	90000
Bob	Sales	70000
Charlie	IT	85000

Task: Show each employee’s salary and their department’s average salary.

Basic Windowing

SELECT
  Name,
  Dept,
  Salary,
  AVG(Salary) OVER(PARTITION BY Dept) AS DeptAvg
FROM Employees;

Output

Name	Dept	Salary	DeptAvg
Alice	IT	90000	87500
Charlie	IT	85000	87500
Bob	Sales	70000	70000

Under the Hood: The Processing Order

Window functions happen very late in the execution of a query:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. WINDOW FUNCTIONS
6. SELECT (Final Projection)
7. ORDER BY

The Filter Trap

Because window functions happen after the WHERE clause, you cannot filter the results of a window function using WHERE.

Incorrect: SELECT Name, RANK() OVER(...) AS MyRank FROM Staff WHERE MyRank = 1;

Solution: You must wrap the query in a CTE or a Subquery and then filter the output of that CTE.

Tip

Think of a Window Function as a “Peeking” operation. The database is processing a row, but it’s allowed to “peek” at other rows in its partition to do a quick calculation before moving on.