Introduction to Window Aggregate Functions

Introduction

Welcome to the world of Window Aggregate Functions! In this lesson, you will learn how to use familiar aggregate functions (COUNT, SUM, AVG, MIN, MAX) in a completely new way using the OVER() clause.

In this lesson, you will learn:

What window aggregate functions are
How they differ from regular aggregate functions with GROUP BY
Why window aggregate functions are powerful for data analysis
When to use window aggregates vs GROUP BY

What are Window Aggregate Functions?

Window aggregate functions are regular aggregate functions (COUNT, SUM, AVG, MIN, MAX) combined with the OVER() clause. This combination gives you the best of both worlds:

Aggregation: Calculate totals, counts, averages like normal
Row Preservation: Keep every individual row in your result

The Key Insight

With GROUP BY, you lose individual rows. With window functions, you keep them.

Approach	Rows in Result	Use Case
`GROUP BY`	One row per group	Summary reports
`OVER()`	All original rows	Detail + Summary together

Visualization

Overview showing the 5 window aggregate functions: COUNT, SUM, AVG, MIN, MAX applied as window functions with OVER clause

Window Aggregate Functions -- Introduction

Regular Aggregate vs Window Aggregate

Let's see the difference with a concrete example.

Regular Aggregate with GROUP BY

This query shows total price per seller, but you lose individual order details:

-- Regular aggregate: 1 row per seller
SELECT 
    seller_id,
    SUM(price) AS total_sales
FROM olist_order_items_dataset
GROUP BY seller_id
LIMIT 10;

Window Aggregate with OVER()

This query shows total price per seller AND keeps every order row:

-- Window aggregate: keeps all rows
SELECT 
    seller_id,
    order_id,
    price,
    SUM(price) OVER (PARTITION BY seller_id) AS seller_total
FROM olist_order_items_dataset
LIMIT 15;

Notice how the window aggregate version:

Shows every individual order with its price
Adds a column with the seller's total sales
Allows you to compare each order to the seller's total

The Five Window Aggregate Functions

You can use any aggregate function as a window function by adding OVER():

Function	Window Syntax	Purpose
COUNT	`COUNT(*) OVER (...)`	Count rows in window
SUM	`SUM(column) OVER (...)`	Sum values in window
AVG	`AVG(column) OVER (...)`	Average values in window
MIN	`MIN(column) OVER (...)`	Minimum value in window
MAX	`MAX(column) OVER (...)`	Maximum value in window

Quick Example: All Five Together

-- Using all five window aggregate functions
SELECT 
    seller_id,
    price,
    COUNT(*) OVER (PARTITION BY seller_id) AS item_count,
    SUM(price) OVER (PARTITION BY seller_id) AS total_price,
    AVG(price) OVER (PARTITION BY seller_id) AS avg_price,
    MIN(price) OVER (PARTITION BY seller_id) AS min_price,
    MAX(price) OVER (PARTITION BY seller_id) AS max_price
FROM olist_order_items_dataset
LIMIT 15;

Why Use Window Aggregate Functions?

Window aggregate functions solve problems that are difficult or impossible with GROUP BY:

1. Compare Individual Values to Group Aggregates

See how each item's price compares to the average for its seller:

-- Compare each item to seller average
SELECT 
    seller_id,
    order_id,
    price,
    AVG(price) OVER (PARTITION BY seller_id) AS seller_avg,
    price - AVG(price) OVER (PARTITION BY seller_id) AS diff_from_avg
FROM olist_order_items_dataset
LIMIT 15;

2. Calculate Running Totals

Track cumulative values as you move through ordered data:

-- Running total of prices
SELECT 
    order_id,
    price,
    SUM(price) OVER (ORDER BY order_id) AS running_total
FROM olist_order_items_dataset
LIMIT 15;

3. Calculate Percentage of Total

Show each value as a percentage of the whole:

-- Each item as percentage of total
SELECT 
    order_id,
    price,
    SUM(price) OVER () AS grand_total,
    ROUND(100.0 * price / SUM(price) OVER (), 4) AS pct_of_total
FROM olist_order_items_dataset
LIMIT 15;

When to Use GROUP BY vs Window Functions

Choose the right tool for the job:

Use GROUP BY When:

You only need summary data (totals, averages, counts)
You want one row per group
You're creating summary reports

Use Window Functions When:

You need individual rows AND aggregates together
You're comparing individual values to group aggregates
You need running totals or moving averages
You want to calculate percentages of totals
You need multiple different groupings in one query

Decision Guide

Question	If Yes →
Do I need individual row details?	Window Function
Do I only need summary values?	GROUP BY
Do I need running/cumulative calculations?	Window Function
Do I need to compare row to group?	Window Function
Am I creating a simple summary report?	GROUP BY

Practice

Practice differentiating between regular aggregates and window aggregates.

Exercise 1: Window vs GROUP BY

Compare how many items each seller has sold, while keeping individual order details:

-- Show each item with the seller's total item count
-- Try modifying this to show different aggregates

SELECT 
    seller_id,
    order_id,
    price,
    COUNT(*) OVER (PARTITION BY seller_id) AS seller_item_count,
    SUM(price) OVER (PARTITION BY seller_id) AS seller_total_sales
FROM olist_order_items_dataset
LIMIT 20;

Exercise 2: Percentage Calculation

Calculate what percentage each payment represents of all payments:

-- Calculate payment as percentage of total

SELECT 
    order_id,
    payment_type,
    payment_value,
    SUM(payment_value) OVER () AS total_all_payments,
    ROUND(100.0 * payment_value / SUM(payment_value) OVER (), 4) AS pct_of_total
FROM olist_order_payments_dataset
LIMIT 15;

Key Takeaways

Here's what you've learned about window aggregate functions:

Window aggregates = Regular aggregates + OVER() clause
Key difference: Window functions keep all rows; GROUP BY collapses them
Five functions: COUNT, SUM, AVG, MIN, MAX work as window functions
Power: Compare individuals to groups, calculate running totals, compute percentages
Choice: Use GROUP BY for summaries, window functions for detail + aggregate together

Coming Up Next

In the following lessons, we'll dive deep into each window aggregate function:

Window COUNT
Window SUM
Window AVG
Window MIN & MAX
Use cases like rolling totals and moving averages