Window Functions

By the end of this lesson you'll write analytics-grade SQL: running totals, rankings, month-over-month comparisons, and moving averages — all while keeping every detail row. The secret is the OVER (...) clause and, above all, the window frame that decides exactly which rows each calculation can see.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

Our Sample Table: sales

Every query in this lesson runs against this tiny sales table — six rows across two regions, one row per region per month. It's small on purpose: you can add the numbers up by hand and confirm each result yourself.

To follow along in a playground, create it with: CREATE TABLE sales (id INT, region TEXT, month INT, amount INT); then insert the six rows above.

1. Window Functions vs GROUP BY

A window function performs a calculation across a set of rows that are related to the current row — its "window" — and returns a value on every row . That's the crucial difference from GROUP BY , which squashes each group down to a single summary row. You reach for window functions whenever you want detail and context in the same result: every sale, with its region's total beside it.

A window function is like looking through a moving train window: the train (your result) keeps all its carriages (rows), but at each seat you can see a few rows of passing scenery. GROUP BY is more like getting off and counting the carriages from the platform — you lose your seat-by-seat view.

2. PARTITION BY and ORDER BY — Steering the Window

Two clauses inside OVER (...) do the steering. PARTITION BY splits the rows into independent groups — the calculation restarts from scratch for each one (here, East is computed entirely separately from West). ORDER BY sets the running order inside each group, and it changes what an aggregate means: the moment you add ORDER BY to SUM() OVER , you get a running total rather than a flat group total.

Read the East rows top to bottom: 100 , then 100+150=250 , then 250+120=370 . West restarts at 200 because PARTITION BY region gives it its own fresh window.

Your Turn: a running total per region

Fill in the two blanks so the total accumulates month by month, separately for each region. The expected numbers are in the comments so you can self-check.

3. Ranking — ROW_NUMBER, RANK, DENSE_RANK, NTILE

Ranking functions assign a position to each row within its partition, in the order you specify. They differ only in how they treat ties (rows with equal ordering values):

• ROW_NUMBER() — a unique number for every row, even on ties (an arbitrary winner is chosen).
• RANK() — ties share a rank, then the next rank skips (1, 2, 2, 4).
• DENSE_RANK() — ties share a rank with no gap after (1, 2, 2, 3).
• NTILE(n) — splits the partition into n roughly equal buckets (e.g. quartiles).

Your Turn: rank each region's months

One blank: choose the ranking function that skips numbers after a tie. The PARTITION BY region ORDER BY amount DESC is already wired up for you.

4. LAG & LEAD — Looking at Neighbouring Rows

LAG(col, n) reaches back to the row n positions earlier ; LEAD(col, n) reaches forward to the row n positions later ( n defaults to 1). They walk the order you give in OVER (...) , restarting at each partition boundary. This is how you compare a row to the one before it — month-over-month change, period-over-period growth, gaps between events.

5. Aggregates as Windows — Moving Averages

Any aggregate — SUM , AVG , COUNT , MIN , MAX — can run as a window. You already built a running SUM . Swap in AVG with a small frame and you get a moving average : a smoothed value over the last few rows. The frame ROWS BETWEEN 1 PRECEDING AND CURRENT ROW means "this row and the one immediately before it" — a 2-row window that slides down the data.

East month 2 averages (100+150)/2 = 125 ; month 3 averages (150+120)/2 = 135 . Month 1 averages only itself because there's no earlier row in its frame.

6. Window Frames — The Part Everyone Skips

The frame is the exact slice of the partition a function can see from the current row. You write it with ROWS BETWEEN <start> AND <end> , where the bounds are:

• UNBOUNDED PRECEDING — the first row of the partition.
• n PRECEDING — n rows before the current row.
• CURRENT ROW — the row being calculated.
• n FOLLOWING — n rows after the current row.
• UNBOUNDED FOLLOWING — the last row of the partition.

So ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is a running total, and ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is the whole-partition total. ( RANGE and GROUPS are sibling frame types that count by value or by ties instead of physical rows — reach for them later; ROWS covers the vast majority of cases.)

Common Errors (and the fix)

• Window function in WHERE : WHERE ROW_NUMBER() OVER (...) = 1 errors with "window functions are not allowed in WHERE" . Window functions run after WHERE , so wrap the query in a CTE or subquery and filter on the alias: WITH ranked AS (SELECT ..., ROW_NUMBER() OVER (...) AS rn FROM sales) SELECT * FROM ranked WHERE rn = 1;
• Missing ORDER BY in a running total: SUM(amount) OVER (PARTITION BY region) with no ORDER BY gives every row the same flat total (370/630), not an accumulating one. Add ORDER BY month to make it run.
• Expecting RANK to be gapless: after a tie, RANK skips (…2, 2, 4…). If you need continuous numbers with no gaps, use DENSE_RANK (…2, 2, 3…). They are not interchangeable.
• The LAST_VALUE default-frame trap: LAST_VALUE(x) OVER (ORDER BY month) returns the current row's value because the default frame ends at CURRENT ROW . Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to reach the real last row.
• Doing NULL maths after LAG : the first row's LAG is NULL , and 150 - NULL = NULL . Use a default ( LAG(amount, 1, 0) ) or wrap with COALESCE(...) if you don't want a NULL result.

📘 Quick Reference

Syntax

Purpose

Frequently Asked Questions

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

Use a window function when you need every detail row and a group-level value beside it (each sale plus its region total, or each row's rank). Use GROUP BY when you only want one summary row per group.

Q: Why can't I put a window function in WHERE or GROUP BY?

Because window functions are evaluated after WHERE , GROUP BY and HAVING — only SELECT and ORDER BY can use them directly. To filter on one, compute it in a CTE/subquery first, then filter on its alias.

Q: RANK vs DENSE_RANK vs ROW_NUMBER — which do I want?

ROW_NUMBER for a strict 1-per-row sequence (e.g. "the single top row"). RANK when ties should share a place and the next place may skip ("joint 2nd, then 4th"). DENSE_RANK when you want tier numbers with no gaps.

No — for running totals the default already gives you start-of-partition → current row. But spell the frame out whenever you use LAST_VALUE , a moving average, or any "whole partition" calc, so the default doesn't quietly cut your window short.

Mini-Challenge: Month-over-Month Change

Support is faded now — a brief, a blank canvas, and the expected answer in the comments. Write it, then copy it into a playground to confirm.

🎉 Lesson Complete

• ✅ Window functions add a value to every row instead of collapsing them like GROUP BY
• ✅ OVER (PARTITION BY ... ORDER BY ...) steers the window; adding ORDER BY makes SUM a running total
• ✅ ROW_NUMBER / RANK / DENSE_RANK / NTILE rank rows and differ only on ties
• ✅ LAG and LEAD reach previous and next rows for period-over-period comparisons
• ✅ The frame ( ROWS BETWEEN ... ) decides exactly which rows a calculation sees — and the default can surprise you
• ✅ Next: package logic on the server with Stored Procedures Advanced

Practice quiz

Continue this course

• Previous: Advanced JOIN Patterns
• Next: Stored Procedures Advanced