You're likely familiar with aggregate functions like SUM() and AVG(). Used with GROUP BY, they are powerful tools for summarizing data. However, they have a limitation: they collapse individual rows into a single summary row. What if you want to perform calculations across a set of rows but keep the details of each row?

This is where window functions come in. They calculate a value over a specified set of rows, called a "window," that are related to the current row. Unlike a GROUP BY aggregation, a window function returns a value for every single row.

The magic of a window function is signaled by the OVER() clause. This clause defines the window of rows the function will operate on. In its simplest form, an empty OVER() clause creates a window that includes all rows in the result set.

In this query, every row will have an avg_company_salary column containing the exact same value: the average salary across the entire employees table. The individual employee rows are preserved.

A single window for the whole table is useful, but the real power comes from dividing the data into smaller, more meaningful windows. We do this with the PARTITION BY clause, which works much like GROUP BY but without collapsing the rows.

PARTITION BY divides the rows into groups, or partitions. The window function is then applied independently to each partition.

Now, the avg_department_salary column shows the average salary for the specific department of the employee in that row. This allows for direct comparison: you can see an individual's salary right next to their department's average, all in one row.

A common analytical task is ranking items within a category. For instance, who are the top 3 salespeople in each region? Window functions make this straightforward. There are three main ranking functions, each handling ties differently.

• ROW_NUMBER(): Assigns a unique, sequential number to each row within the partition. It never produces duplicate numbers.
• RANK(): Assigns the same rank to rows with the same value. It then skips the next rank(s) to account for the tie. Notice how it jumps from rank 2 to 4 in the table.
• DENSE_RANK(): Also assigns the same rank to ties, but it does not skip the next rank. It's "dense" because the ranks are always consecutive.

This query ranks employees within each department based on their sales, from highest to lowest. The ORDER BY clause inside OVER() is crucial; it tells the ranking function how to order the rows before assigning ranks.

Sometimes you need to compare a row's value with the value in a preceding or succeeding row. This is common for calculating period-over-period changes, like month-over-month sales growth. Positional functions LAG and LEAD are designed for this.

Think of LAG as looking backward and LEAD as looking forward within your ordered partition.

This query retrieves each month's revenue and puts the previous month's revenue right next to it in the same row. From there, calculating the difference or percentage change is a simple arithmetic operation.

Finally, you can get even more specific about which rows are included in your window's calculation by defining a "frame." The frame is a moving subset of rows within a partition. You define it using clauses like ROWS BETWEEN.

This is most often used for calculating moving averages, where you might want to average the current row's value with the two preceding rows.

Here, for each day, the window function calculates the average revenue of that day and the two days before it. The ROWS BETWEEN clause defines this sliding 3-day frame. This is a powerful technique for smoothing out noisy data and identifying trends.

Let's test your understanding of these functions.

Window functions open up a new level of analytical capability directly within your database, allowing for complex comparisons and calculations that would otherwise require multiple queries or post-processing.