Learn Introduction to Window Functions | Window Functions for Trends and Rankings

Window functions are a powerful feature in SQL that allow you to perform calculations across sets of rows related to the current row, without collapsing the result into a single output row as standard aggregations do. Unlike GROUP BY, which summarizes data into groups and returns one row per group, window functions let you keep the original row structure while adding calculated values alongside each row. This is especially important in business intelligence (BI) where you often need to see both the detail and the summary for trend analysis, rankings, and moving averages.


              12
            
SELECT sale_id, amount, AVG(amount) OVER () AS avg_order
FROM sales;

In this example, you are calculating the average sale amount across all sales using the AVG(amount) OVER () window function. The OVER() clause tells SQL to compute the average over all rows, but unlike a grouped aggregation, it does not collapse the rows. Instead, each sale row gets an additional column showing the overall average. This approach is different from using GROUP BY, which would return only one row for the entire table if you grouped by nothing.

The OVER() clause is what makes a function a window function. It defines the "window" of rows that the function should consider for its calculation. If you leave the parentheses empty, the function considers all rows in the result set. You can also specify partitions or orderings inside OVER() to adjust which rows are included in the window for each calculation.


              123
            
SELECT employee_id, sales_achieved, 
       SUM(sales_achieved) OVER (ORDER BY review_date_id) AS running_total
FROM employee_performance;

Here, you see a classic use of window functions for calculating running totals. The SUM(sales_achieved) OVER (ORDER BY review_date_id) function creates a cumulative sum of sales_achieved as you move through each review_date_id. This running total is useful for tracking progress over time, such as monitoring how individual or team sales accumulate during a quarter or year. In BI, running totals help you visualize trends, spot momentum changes, and compare actual performance against targets.

Definition

Window functions are SQL calculations that operate across a set of table rows that are somehow related to the current row. They do not collapse rows like aggregations with GROUP BY, but instead return a value for every row in the original query. In business intelligence, window functions are essential for trend analysis, ranking, and generating advanced metrics without losing the underlying data detail.

You will frequently use window functions to calculate moving averages, running totals, and percentiles. These calculations are vital for BI analysts who need to spot trends, compare performance, and build dashboards that show both individual records and overall patterns. For example, a moving average smooths out daily sales fluctuations, a running total shows cumulative progress, and percentiles help you identify top performers.

Everything was clear?

Thanks for your feedback!

Section 3. Chapter 1

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Swipe to show menu


              12
            
SELECT sale_id, amount, AVG(amount) OVER () AS avg_order
FROM sales;


              123
            
SELECT employee_id, sales_achieved, 
       SUM(sales_achieved) OVER (ORDER BY review_date_id) AS running_total
FROM employee_performance;

Definition

Everything was clear?

Thanks for your feedback!

Section 3. Chapter 1

Introduction to Window Functions

1. What is the main advantage of window functions over GROUP BY?

2. How does the OVER() clause work in SQL?

3. Give an example of a business metric calculated with a window function.

Introduction to Window Functions

1. What is the main advantage of window functions over GROUP BY?

2. How does the OVER() clause work in SQL?

3. Give an example of a business metric calculated with a window function.