Introduction to Modern Databases/

...

/

Window Functions

Window Functions

Learn how window functions enhance SQL queries with rankings, running totals, and lookahead comparisons.

We'll cover the following...

Consider a scenario in which we are analyzing a Products table and aim to identify the three most expensive products within each category.

One might initially attempt to use a GROUP BY clause to determine the maximum price per category. However, this approach yields only the highest price value for each group and does not reveal which specific products correspond to those values.

Alternatively, constructing a nested subquery could achieve the desired result, but such a solution would quickly become complex and difficult to manage.

This is precisely where window functions prove valuable. Window functions enable the execution of sophisticated calculations—such as ranking, running totals, or cumulative comparisons—across a defined subset of rows related to each record, referred to as a window.

Unlike aggregate functions, window functions do not collapse rows into a single result per group; instead, they preserve the granularity of the original dataset while augmenting each row with computed values derived from its surrounding context.

By the end of this lesson, we will be able to:

  • Understand what window functions are and why they are so useful.

  • Use the OVER() clause to define a “window” of data.

  • Divide data into groups using PARTITION BY.

  • Apply common window functions like RANK(), DENSE_RANK(), ROW_NUMBER(), LEAD(), and LAG() to perform advanced analysis.

Let’s dive in and see how we can level up our SQL skills!

Window functions

Think of it as a special type of ...