Back to Topics

Window Functions

Advanced
16 min

What is Window Functions?

Window functions perform calculations across a set of rows related to the current row, without collapsing them like GROUP BY.

Key Components:

  • Calculates across related rows
  • Does not collapse rows
  • Adds ranking, running totals
  • Partitions data into groups

Why it matters

Ranking

Rank products by sales

Running Totals

Cumulative sales over time

Comparisons

Compare row to previous row

Percentages

Percent of total calculation

Key Concepts

ROW_NUMBER

Unique sequential number

Example: ROW_NUMBER() OVER(...)...

RANK

Rank with gaps

Example: RANK() OVER(...)...

OVER()

Defines window

Example: OVER(ORDER BY Sales)...

PARTITION BY

Group within window

Example: OVER(PARTITION BY Category)...

How to use

1

Choose function

ROW_NUMBER, RANK, SUM, etc.

2

Add OVER clause

Define window

3

Use PARTITION BY

Optional grouping

4

Use ORDER BY

Order within window

5

Reference in SELECT

Include in output

6

Execute query

See results with additional column

Example

Goal: Rank products by sales
SELECT Product, Sales, RANK() OVER (ORDER BY Sales DESC) as SalesRank FROM Products
Result: Products ranked from highest to lowest sales

Pro Tips

  • •PARTITION BY creates groups: Reset calculation per group
  • •ORDER BY matters: Affects ranking order
  • •Combine functions: Use multiple window functions

Practice

Calculate running total of sales by date for each product category
PreviousAll Topics