Window Functions Explained Like You're 10

The classroom explanation

Imagine a teacher just handed back a spelling test to a class of six kids. She wants every kid to see two things on their own paper: their own score, and their class's average score — without erasing anyone's name or merging two kids onto one paper.

That's exactly what a window function does. It "looks through a window" at a group of related rows — in this case, everyone in the same class — calculates something across them, and writes the answer next to every original row, instead of collapsing them into a single summary.

Here's the class roster we'll use for every example on this page:

Six kids, two classes (5A and 5B), one spelling test.

Why not just use GROUP BY?

GROUP BY can absolutely calculate a class average — but it can only give you one row per class, and it throws away every individual kid's name and score to do it:

SELECT class, AVG(score) AS class_avg
FROM spelling_test
GROUP BY class;

Mia, Leo, and Ana are gone. If the teacher wants each kid's own row to also show the class average next to it, GROUP BY can't do that on its own — this is precisely the gap a window function fills.

The OVER() clause, piece by piece

A window function looks like a normal aggregate function (AVG, SUM, COUNT), plus an OVER() clause that tells it which rows count as "the window":

AVG(score) OVER (
  PARTITION BY class   -- split into windows: one per class
  ORDER BY student_id  -- optional: order rows within each window
)

Both PARTITION BY and ORDER BY inside OVER() are optional. With neither, the window is the entire result set — every row sees every other row.

PARTITION BY — splitting into windows

PARTITION BY class tells the database: "calculate this separately for each class, and don't let 5A's scores leak into 5B's average." Every kid keeps their own row — they just get their class average attached:

SELECT name, class, score,
       AVG(score) OVER (PARTITION BY class) AS class_avg
FROM spelling_test;

Six rows in, six rows out — every kid keeps their name and score, and now knows exactly how they compare to their own class's average.

ORDER BY inside OVER() — running totals

Add an ORDER BY inside OVER() and the window function stops looking at "everyone" and starts looking at "everyone so far." This is how running totals work — each row's window is every row from the start up to itself:

SELECT name, score,
       SUM(score) OVER (ORDER BY student_id) AS running_total
FROM spelling_test;

Each row adds its own score to the sum of every row before it — like adding coins to a jar one at a time and writing down the new total after each coin.

A few window functions worth knowing

Almost any aggregate function (SUM, AVG, COUNT, MIN, MAX) can be used as a window function just by adding OVER(). On top of those, SQL has functions that only make sense as window functions:

Here's LAG() in action — showing how each kid's score compares to the kid listed right before them:

SELECT name, score,
       LAG(score) OVER (ORDER BY student_id) AS previous_score
FROM spelling_test;   -- Leo's previous_score is Mia's 92

Putting it all together

The real power shows up when you combine PARTITION BY and ORDER BY in the same window. Here's every kid ranked within their own class, highest score first — using ROW_NUMBER():

SELECT name, class, score,
       ROW_NUMBER() OVER (
         PARTITION BY class
         ORDER BY score DESC
       ) AS class_rank
FROM spelling_test;

The ranking resets to 1 for each new class — that's PARTITION BY at work — and within each class, the highest scorer comes first — that's the ORDER BY. Every kid still has their own row.

Common mistakes

• Trying to filter a window function in WHERE. WHERE class_rank = 1 fails, because window functions are calculated during SELECT — after WHERE has already run. Wrap the query in a subquery or CTE and filter in the outer query instead. (Full explanation in SQL execution order explained.)
• Forgetting PARTITION BY. Without it, the window is the whole table — a "running total" without PARTITION BY runs across every row, not per group.
• Confusing the OVER() ORDER BY with the query's ORDER BY. They're independent. The one inside OVER() controls calculation order; the one at the end of the query controls display order.