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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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.

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.

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.

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.

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):

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.

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.

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.

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:

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.)

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.

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.

Practice quiz

How does a window function differ from GROUP BY?

  • It deletes rows
  • It can only count rows
  • It returns a value on every row instead of collapsing rows into one
  • It requires no SELECT

Answer: It returns a value on every row instead of collapsing rows into one. GROUP BY squashes each group to one row; a window function keeps every row and adds a value.

What does PARTITION BY do inside an OVER clause?

  • Splits rows into independent groups, restarting the calculation per group
  • Sorts the final output
  • Removes duplicate rows
  • Limits the number of rows returned

Answer: Splits rows into independent groups, restarting the calculation per group. PARTITION BY divides rows into groups; the window calculation restarts for each.

Adding ORDER BY to SUM(amount) OVER (PARTITION BY region ...) turns it into what?

  • A flat group total repeated on every row
  • A row count
  • An error
  • A running (cumulative) total

Answer: A running (cumulative) total. With ORDER BY, the aggregate accumulates from the start of the partition to the current row.

Which ranking function gives a unique number to every row, even on ties?

  • RANK()
  • ROW_NUMBER()
  • DENSE_RANK()
  • NTILE()

Answer: ROW_NUMBER(). ROW_NUMBER() always produces a strict 1,2,3 sequence with no shared numbers.

For scores 90, 85, 85, 70, what does RANK() produce?

  • 1, 2, 2, 4
  • 1, 2, 2, 3
  • 1, 2, 3, 4
  • 1, 1, 2, 3

Answer: 1, 2, 2, 4. RANK shares the rank on ties then skips: 1, 2, 2, 4.

For the same scores 90, 85, 85, 70, what does DENSE_RANK() produce?

  • 1, 2, 2, 4
  • 1, 2, 3, 4
  • 1, 2, 2, 3
  • 1, 1, 2, 2

Answer: 1, 2, 2, 3. DENSE_RANK shares the rank on ties with no gap after: 1, 2, 2, 3.

What does LAG(amount, 1) return?

  • The value of the next row
  • The value of the row 1 position earlier in the window
  • The largest value in the partition
  • The current row's value doubled

Answer: The value of the row 1 position earlier in the window. LAG reaches back to an earlier row; LEAD reaches forward to a later row.

On the first row of a partition, what does LAG return (with no default supplied)?

  • 0
  • The current value
  • An error
  • NULL

Answer: NULL. There is no earlier neighbour, so LAG returns NULL unless you pass a default like LAG(amount, 1, 0).

What does the frame 'ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW' describe?

  • The whole partition
  • From the first row of the partition down to the current row (a running total)
  • A 2-row moving window
  • Only the current row

Answer: From the first row of the partition down to the current row (a running total). It spans from the start of the partition to the current row, i.e. a running total.

Why does putting a window function directly in a WHERE clause fail?

  • Window functions are deprecated
  • WHERE only accepts aggregates
  • Window functions are evaluated after WHERE, so you must filter in a CTE/subquery on the alias
  • It needs an index first

Answer: Window functions are evaluated after WHERE, so you must filter in a CTE/subquery on the alias. Window functions run after WHERE/GROUP BY/HAVING; wrap the query and filter on the computed alias.