How do window functions differ from GROUP BY?

GROUP BY groups multiple rows into a single summary row, collapsing detail. Window functions compute values across groups of rows (partitions) while retaining every individual detail row in the output.

What is the default window frame if omitted?

When an ORDER BY is specified in OVER() without an explicit frame clause, the ANSI standard default is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. This default can cause performance issues and unexpected duplicate sums when ties exist. Usually, `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` is preferred.

What is the difference between ROWS and RANGE in window frames?

ROWS defines the frame based on physical row counts (e.g. 5 physical rows preceding). RANGE defines the frame based on logical value offsets matching the current row's ORDER BY value, treating tied values as a single unit.

Where do window functions execute in the SQL query lifecycle?

Window functions execute late in the query lifecycle: AFTER FROM, WHERE, GROUP BY, and HAVING, but BEFORE ORDER BY and LIMIT. Because of this, you cannot filter window function results in a WHERE clause directly; you must use a CTE, subquery, or BigQuery QUALIFY.

SQL Dialects

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

By Sharon Ben-Moshe · October 13, 2026 · 2 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

The comprehensive guide to SQL window functions. Master OVER, PARTITION BY, ORDER BY, window frames (ROWS vs RANGE), and ranking recipes.

Quick answer

A SQL window function calculates aggregate or ranking values across a defined set of rows (a 'window') related to the current row without collapsing the result set into a single summary line. Unlike GROUP BY, window functions retain individual row identities while evaluating running totals, period-over-period differences, and rankings via the OVER() clause.

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

Quick answer

FAQs

How do window functions differ from GROUP BY?

What is the default window frame if omitted?

What is the difference between ROWS and RANGE in window frames?

Where do window functions execute in the SQL query lifecycle?

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

BigQuery STRUCT vs ARRAY: Handling Nested and Repeated Data

Key takeaways

Anatomy of the OVER() clause

The 3 functional categories of window functions

1. Ranking functions

2. Positional value functions

3. Aggregate window functions

Real-world recipes: Running balances and moving averages

Official references

Quick answer

FAQs

How do window functions differ from GROUP BY?

What is the default window frame if omitted?

What is the difference between ROWS and RANGE in window frames?

Where do window functions execute in the SQL query lifecycle?

Keep reading

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

BigQuery STRUCT vs ARRAY: Handling Nested and Repeated Data

Key takeaways

Anatomy of the OVER() clause

The 3 functional categories of window functions

1. Ranking functions

2. Positional value functions

3. Aggregate window functions

Real-world recipes: Running balances and moving averages

Related SQL guides

Official references