Introduction to Window Functions

Window functions perform calculations across a set of table rows related to the current row, without collapsing the results like aggregate functions do.

What Makes Window Functions Different?

Unlike regular aggregate functions, window functions return a value for every row while considering a "window" (group) of rows around it.

Common Window Functions

• RANK() — Assigns ranks to rows based on order
• ROW_NUMBER() — Assigns unique row numbers
• SUM() OVER(...), AVG() OVER(...) — Aggregate functions used as window functions

Example: Ranking Students by Age

SELECT name, age,
RANK() OVER (ORDER BY age DESC) AS age_rank
FROM students;

This query ranks students by age, showing who is oldest (rank 1) and so on.

Window functions are powerful tools for advanced data analysis and reporting.

‍