Introduction to Window Functions

Window functions add a new layer of power to SQL by calculating values across a set of rows related to the current row — without collapsing them into a single output.

Unlike aggregate functions that group rows together, window functions preserve individual rows while adding calculated columns such as ranks, running totals, or differences.

Traditional SQL can be limiting when you need to compare or rank rows while still keeping individual records visible.

• Assign ranks or row numbers within groups
• Compare a row to its neighbors
• Compute running totals or moving averages
• Access previous or next row values

This is especially useful in analytics, reporting, and dashboards, where row-level context is important.

Consider this question:

"What is each student's grade and how does it compare to the class average?"

A regular GROUP BY query would return the average per class, but not retain individual student records. A window function allows you show both the student's grade and the class average in the same row.

Window functions use the OVER clause to define the scope of the “window” — the set of rows the function considers.

It can include:

• PARTITION BY — to restart the calculation for each group (like a department or course)
• ORDER BY — to define sequence for calculations like rankings or running totals

Check out the slide deck on the right to explore syntax and real-world examples like RANK(), ROW_NUMBER(), and how the OVER() clause shapes their behavior.