SQL Window Functions: A Practical Guide

SQL Window Functions: A Practical Guide

SQL window functions add per-row calculations — rankings, running totals, comparisons to the previous row — without collapsing your result set the way GROUP BY does. If you’ve ever wanted a column that says “rank within this group” while keeping every original row, this guide shows you how to write the patterns you’ll reach for weekly.

In this guide
  1. What a window function is (and why it’s not GROUP BY)
  2. The anatomy of OVER(): PARTITION BY and ORDER BY
  3. Ranking rows: ROW_NUMBER, RANK, and DENSE_RANK
  4. Running totals and moving averages
  5. Looking at other rows: LAG and LEAD
  6. Worked example: top-N per group
  7. Common pitfalls to avoid
  8. FAQ
Quick answer: A window function computes a value for each row using a related set of rows — its “window” — and adds it as a new column without removing any rows. Declare the window with OVER(). PARTITION BY (optional) divides rows into independent groups; ORDER BY inside OVER() sets the sort order for ranking and cumulative calculations. Row count never changes.

What a window function is (and why it’s not GROUP BY)

GROUP BY collapses rows. Ask for revenue by country and you get one row per country — the individual orders are gone. That’s exactly right for a summary, but not when you also need the row-level detail.

A window function keeps every row and attaches a computed value alongside it. The “window” is the set of rows the function looks at when calculating the current row’s value — the whole result, a group of rows sharing a common column value, or a sliding frame of nearby rows. Window functions never reduce row count.

The anatomy of OVER(): PARTITION BY and ORDER BY

Every window function requires OVER() — that’s the clause that turns an aggregate call into a window calculation. Inside sit two optional pieces:

  • PARTITION BY – divides the result into independent groups. The function restarts its calculation for each partition. Omit it and the whole result set is treated as one window.
  • ORDER BY (inside OVER()) – sets the row order within each partition. For ranking functions it determines what gets rank 1; for running totals it sets the cumulative direction.

Three versions of the same function: SUM(amount) OVER() adds the grand total to every row. OVER(PARTITION BY country) adds each country’s subtotal. OVER(PARTITION BY country ORDER BY order_date) builds a running total per country in date order. Same function, three windows.

Ranking rows: ROW_NUMBER, RANK, and DENSE_RANK

All three assign a position to each row within a partition, but they handle ties differently. Two employees scored 90; one scored 85:

EmployeeScoreROW_NUMBERRANKDENSE_RANK
Alice90111
Bob90211
Carol85332

ROW_NUMBER always produces unique sequential numbers: 1, 2, 3 with no repeats. RANK gives tied rows the same rank and then skips — two rows at 1 means the next rank is 3. DENSE_RANK shares the rank for ties without a gap: two 1s, then 2.

Use ROW_NUMBER when you need exactly one result per group — the worked example below explains why. Use DENSE_RANK when “second place” should mean the second distinct score tier, not the third physical row. All three require ORDER BY inside OVER().

Running totals and moving averages

SUM, AVG, MIN, and MAX all work as window functions. The workhorse is a running total: SUM(amount) OVER(PARTITION BY user_id ORDER BY order_date) gives each row the cumulative spend for that user up to that date.

The default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. For unique ORDER BY values it works as expected. When two rows share the same date, RANGE treats them as one group and sums both at once, making the running total jump rather than increment row by row. Switching to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW processes each physical row individually. Moving averages use the same syntax with a bounded frame: AVG(amount) OVER(ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).

Looking at other rows: LAG and LEAD

LAG(column, n, default) returns the value from n rows before the current row within the window; LEAD does the same forward. Both default to an offset of 1 and return NULL when no prior or following row exists — unless you supply a third argument as the fallback.

The main use is period-over-period comparison without a self-join. LAG(revenue) OVER(PARTITION BY region ORDER BY month) pulls last month’s revenue into the current row, so month-over-month change becomes a simple subtraction in the same SELECT. LEAD works forward — useful for computing time between consecutive events.

Worked example: top-N per group

Selecting the top-N rows per group — latest order per customer, highest score per category — is the most common real-world window pattern. GROUP BY can’t do it cleanly. A CTE with ROW_NUMBER() can: put ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date DESC) AS rn in the CTE, then filter with WHERE rn = 1 in the outer query. The PARTITION BY restarts numbering for each customer; DESC ordering makes the newest row 1; and because ROW_NUMBER always produces unique values, you always get exactly one row per customer.

The same shape handles de-duplication: partition by the columns that define a duplicate, order by the column that picks the correct copy — a timestamp, a version number — and keep rn = 1.

Common pitfalls to avoid

WHERE can’t filter a window result. Window functions run after WHERE has filtered rows, so WHERE rn = 1 in the same SELECT that defines rn fails. Fix: put the window function in a CTE or subquery and filter in the outer query. Some databases add a QUALIFY clause as shorthand, but it isn’t standard SQL — the CTE is portable.

Default frame surprises. Adding ORDER BY inside OVER() without an explicit frame clause defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. On unique sort values that’s fine; on duplicate sort values it can cause running totals to jump unexpectedly. Writing ROWS rather than RANGE and stating the frame explicitly removes the ambiguity.

Two ORDER BYs, two jobs. ORDER BY inside OVER() controls the window calculation order. ORDER BY at the end of the query controls final output order. They are independent.

Frequently asked questions

Can I use a window function in a WHERE clause?

No — window functions evaluate after WHERE, so they can’t be referenced there directly. Wrap the window function in a CTE or subquery and filter on its output in the outer query. Some warehouse dialects add QUALIFY as shorthand, but it isn’t standard SQL; the CTE approach works everywhere.

What is the difference between RANK and DENSE_RANK?

Both assign tied rows the same rank. RANK then skips the next position — two rows tied at 1 mean the next rank is 3. DENSE_RANK leaves no gap — two 1s are followed by 2. Use DENSE_RANK when you want to count distinct performance tiers; use RANK when position gaps should appear in the output.

Do window functions work in all SQL databases?

All major modern databases support the core window functions covered here — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and aggregate windows. This includes PostgreSQL, MySQL 8+, SQL Server 2012+, BigQuery, Snowflake, Redshift, and DuckDB. The main exceptions are MySQL pre–8.0 and very early SQLite versions.

When should I use GROUP BY instead of a window function?

Use GROUP BY when you want a summary with fewer rows than the source — totals or counts per category. Use a window function when you need to keep every original row and add a computed column. If a single query needs both — say, a row-level rank and a category total — a CTE for each step combines them cleanly.

Window functions are the upgrade that turns solid SQL into genuinely analytical queries. Once OVER(), PARTITION BY, and ORDER BY feel familiar, the patterns in this guide come quickly. The modern data stack guide sets the context for where these queries run; star schema explained covers the table designs that make window functions especially useful day-to-day.

Now a book: The Data Engineer's Blueprint The whole DataStack Daily series, rebuilt into one plain-English guide to the modern data stack — warehouses, pipelines, dbt, SQL, and dashboards. Paperback & Kindle. Get it on Amazon →

Keep reading: Star Schema Explained: Fact and Dimension Tables · Slowly Changing Dimensions (SCD Types 1, 2, and 3) · The Modern Data Stack Explained (Plain English)

Last updated: July 6, 2026

Comments

Post a Comment

Popular posts from this blog

The Modern Data Stack Explained (Plain English)

Image
The modern data stack is the set of cloud tools teams use to move data from where it’s created to where people can actually use it — dashboards, reports, and models. If you’re new to data and the jargon feels like alphabet soup, this guide is the map. We’ll walk each layer in plain English, follow one order all the way from a store database to a dashboard, and point you to where to start. In this guide What 'the modern data stack' actually means The five layers, from source to dashboard Ingestion: getting data in Storage: the warehouse or lakehouse Transformation: turning raw into useful BI and activation: where people see it How the pieces fit together (a worked example) Where to start if you're new FAQ Quick answer: The modern data stack is a set of specialized cloud tools — ingestion, storage, transformation, and BI — that together turn scattered raw data into trustworthy numbers people can query. Instead of one big monolith, each...
Read more

Data Warehouse vs Data Lake vs Lakehouse

Image
‘Data warehouse vs data lake’ trips up almost everyone new to the field, partly because people use the two words as if they mean the same thing. They don’t. A warehouse stores clean, structured tables ready for analysis; a lake stores raw files of any shape for later. This guide explains both (plus the newer lakehouse), who uses each, and how to pick — in plain English. In this guide The three terms, defined Structured vs unstructured: the core split The warehouse: fast, clean, pricey The lake: cheap, raw, flexible The lakehouse: the merge A comparison table Which one you actually need FAQ Quick answer: A data warehouse stores structured, cleaned data optimized for fast SQL analytics; a data lake stores raw data of any type cheaply for flexible, later use. A lakehouse combines the two — lake-style storage with warehouse-style querying layered on top. The three terms, defined The data warehouse vs data lake question is really about how mu...
Read more

ETL vs ELT: What's the Difference (and Which)?

Image
‘ETL vs ELT’ is one of the most common interview questions in data — and one of the most over-complicated. The difference comes down to a single letter’s worth of ordering: do you transform data before you load it, or after? This guide is for beginners who want the real distinction, the reason the default flipped, and a straight answer on which to choose. In this guide ETL and ELT in plain terms The one difference that matters: order of operations Why cloud warehouses changed the default Trade-offs side by side When ETL still wins When ELT is the better call What most teams do in 2026 FAQ Quick answer: ETL transforms data before loading it into the warehouse; ELT loads raw data first and transforms it inside the warehouse. Same three steps — extract, transform, load — in a different order, and today ELT is the more common default for cloud analytics. ETL and ELT in plain terms Both terms describe the same three jobs: Extract data f...
Read more