Introduction to Window Functions

Add aggregated or ranked values to every row — without collapsing anything

Advanced 18 minOVERPARTITION BYwindow

The OVER() Clause

Window functions compute a value across a set of related rows without collapsing them — unlike GROUP BY which merges rows into groups. Every original row stays in the output and gets an additional computed column. Here's how the OVER() clause works:

OVER() = entire result set — The window spans all rows. SUM(total) OVER () adds a grand total column to every single row, letting you see each row's value alongside the overall total.

PARTITION BY = group without collapsing — SUM(total) OVER (PARTITION BY category) computes category totals while keeping every individual row intact. Like GROUP BY but without losing the row-level detail.

ORDER BY in OVER = running calculations — SUM(total) OVER (ORDER BY date) creates a running total that accumulates row by row. Add PARTITION BY to get running totals that reset per group.

Timing matters — Window functions execute after WHERE and GROUP BY are complete, but before ORDER BY and LIMIT. This means you cannot use window functions inside WHERE clauses directly.

Knowledge Canvas

How Window Functions Work

Calculate across rows without collapsing them

Add computed columns WITHOUT GROUP BY collapsing
OVER() defines the window: PARTITION BY + ORDER BY
Each row sees a "frame" of related rows
Runs after WHERE, GROUP BY, HAVING — just before ORDER BY

The Window Concept

Think of a sliding frame over your data

Imagine looking through a window at your sorted data — the function computes over whatever the window reveals.

PARTITION BY = divide rows into groups (like GROUP BY but no collapse)
ORDER BY = define sequence within each partition
Frame = which rows in the partition are visible to the function
Default frame: UNBOUNDED PRECEDING to CURRENT ROW

Query Execution Order

Where window functions fit

FROM / JOIN — pick tables

WHERE — filter rows

GROUP BY — collapse groups

HAVING — filter groups

Window Functions — compute over result set

SELECT — produce output columns

ORDER BY / LIMIT — final sort and cap

Window vs Aggregate

Key difference

Aggregate: collapses rows into groups

Window: adds columns to existing rows

SUM(price) → one total

SUM(price) OVER() → running total per row

Fewer rows in output

Same row count as input

When to Use Windows

Classic scenarios

Running totals and moving averages
Rank within groups (top N per category)
Compare current row to previous/next
Percent of total calculations
Deduplication with ROW_NUMBER

Syntax

Syntax Template

1SELECT

2 col,

3 SUM(col) OVER () AS grand_total,

4 SUM(col) OVER (

5 PARTITION BY grp

6 ) AS group_total,

7 SUM(col) OVER (

8 PARTITION BY grp

9 ORDER BY dt

10 ) AS running

11FROM table;

OVER ()Window = all rows

PARTITION BYSplits into groups without collapsing

ORDER BY in OVERRow ordering for running calculations

Sample Data

orders

3 rows

customer_id	order_date	total
C1001	2024-01-15	59.98
C1001	2024-02-20	12.5
C1002	2024-02-08	449

products

10 rows

id	name	category	price	stock
P101	Wireless Mouse	Electronics	24.99	150
P102	Office Desk	Furniture	189	25
P103	LED Desk Lamp	Furniture	45.5	80
P104	USB-C Cable	Electronics	12.99	500
P105	Bluetooth Speaker	Electronics	79	60
P106	Office Chair	Furniture	95	15
P107	Coffee Maker	Appliances	89.99	40
P108	Notebook Set	Stationery	8.5	200
P109	Wireless Headphones	Electronics	159	30
P110	Standing Desk	Furniture	425	8

Worked Example

Show each order with a running total per customer.

SQL

1SELECT

2 customer_id,

3 order_date,

4 total,

5 SUM(total) OVER (

6 PARTITION BY customer_id

7 ORDER BY order_date

8 ) AS running_total

9FROM orders

10WHERE customer_id IN ('C1001', 'C1002')

11ORDER BY

12 customer_id,

13 order_date;

PARTITION BY customer_id creates separate windows. ORDER BY order_date makes SUM accumulate chronologically. Customer C1001: 59.98 → 72.48 → 162.47. Customer C1002: 449.00 → 563.98. Each row shows the cumulative spend so far for that customer.

Output

3 rows

customer_id	order_date	total	running_total
C1001	2024-01-15	59.98	59.98
C1001	2024-02-20	12.5	72.48
C1002	2024-02-08	449	449

Key Concepts

1Window functions keep every row — GROUP BY collapses them

2OVER() defines the window: which rows the function considers

3PARTITION BY = GROUP BY without collapsing

4Cannot use window functions in WHERE — they run after filtering

Pro Tip

GROUP BY forces you to collapse rows — you lose detail. Window functions keep every row while adding aggregated context. This is the most powerful analytical feature in SQL.

When to Use

Running totals, moving averages, ranking within categories, comparing each row to its group average, cumulative distributions.

Challenge

Solve the problem below

Show each product with its name, category, price, and the average price within its category. Name the column category_avg_price (rounded to 2 decimals).

Your Query

Common Table Expressions (CTEs)

ROW_NUMBER()