Aggregates Window Function

Learn how to use Aggregates Window Functions in SQL to perform advanced data analysis and calculations. Discover how these powerful functions enhance performance and help you analyze complex datasets with ease.

Aggregates in SQL Window Functions

In SQL, aggregate functions are used to perform calculations on multiple rows of data and return a single value, like the total sum, average, or count of a column. However, when used as window functions, these aggregates allow you to compute values over a “window” of rows while keeping the original rows intact. This gives you the flexibility to perform calculations within groups of data without losing individual row information.

Common aggregate functions used as window functions include:

• SUM(): Calculates the total sum of a specified column.
• AVG(): Computes the average value of a column.
• MIN(): Returns the smallest value in a column.
• MAX(): Returns the largest value in a column.
• COUNT(): Counts the number of rows or non-NULL values in a column.

Basic Syntax for Window Aggregate Functions

sqlCopy codeSELECT 
    column1, 
    column2, 
    SUM(column2) OVER (PARTITION BY column1 ORDER BY column2) AS SumResult,
    AVG(column2) OVER (PARTITION BY column1 ORDER BY column2) AS AvgResult,
    MIN(column2) OVER (PARTITION BY column1 ORDER BY column2) AS MinResult,
    MAX(column2) OVER (PARTITION BY column1 ORDER BY column2) AS MaxResult,
    COUNT(column2) OVER (PARTITION BY column1 ORDER BY column2) AS CountResult
FROM table_name;

Here:

• PARTITION BY divides the result set into groups.
• ORDER BY orders the data within those groups.

Examples of Aggregate Window Functions

Let’s assume we have a table of sales data:

1. SUM() Example (Cumulative Sales)

Let’s calculate the total sales for each department:

SELECT 
    SaleDate, 
    SalesAmount, 
    Department,
    SUM(SalesAmount) OVER (PARTITION BY Department ORDER BY SaleDate) AS CumulativeSales
FROM Sales;

In this example, the SUM(SalesAmount) is calculated for each department, and the cumulative sales amount increases with each sale within that department.

2. AVG() Example (Average Sales per Department)

Let’s calculate the average sales amount for each department:

SELECT 
    SaleDate, 
    SalesAmount, 
    Department,
    AVG(SalesAmount) OVER (PARTITION BY Department ORDER BY SaleDate) AS AvgSales
FROM Sales;

The AVG(SalesAmount) computes the average sales within each department up to each row’s position.

3. MIN() and MAX() Example (Min and Max Sales in Each Department)

Let’s find the minimum and maximum sales amounts within each department:

SELECT 
    SaleDate, 
    SalesAmount, 
    Department,
    MIN(SalesAmount) OVER (PARTITION BY Department) AS MinSales,
    MAX(SalesAmount) OVER (PARTITION BY Department) AS MaxSales
FROM Sales;

MIN(SalesAmount) and MAX(SalesAmount) show the lowest and highest sales amounts for each department across all rows.

4. COUNT() Example (Number of Sales Per Department)

Let’s count how many sales transactions occurred within each department:

SELECT 
    SaleDate, 
    SalesAmount, 
    Department,
    COUNT(SalesAmount) OVER (PARTITION BY Department) AS NumSales
FROM Sales;

COUNT(SalesAmount) counts how many sales entries exist for each department.

Common Interview Questions Based on Aggregates

1. What is the difference between GROUP BY and PARTITION BY in SQL?
   • Answer: GROUP BY groups rows together and collapses them into a single summary row per group, whereas PARTITION BY divides the rows into groups but allows for calculations to be done on each row within those groups (without collapsing them).
2. How do you calculate the average sales per month using a window function?
   • Answer: Partition by month (and year if necessary) and then calculate the average.
3. How would you calculate the running total of sales for each department?
   • Answer: Use the SUM() window function with PARTITION BY Department and ORDER BY SaleDate to calculate the cumulative sum.
   SELECT SaleDate, SalesAmount, Department, SUM(SalesAmount) OVER (PARTITION BY Department ORDER BY SaleDate) AS RunningTotal FROM Sales;
4. What is the use of the COUNT() window function in SQL?
   • Answer: COUNT() in a window function counts the number of rows within each partition, providing insights such as how many entries are in each group (e.g., how many sales transactions occurred within each department).
5. Can you use window functions with aggregate functions like SUM() or AVG() in SQL?
   • Answer: Yes, window functions allow you to use aggregate functions like SUM(), AVG(), MIN(), MAX(), and COUNT() to compute values over a specified window or group of rows, without collapsing the rows into a single value.

SQL