HumachLearnLearn · explore · apply
Start learning
SQL for Analytics · Lesson 38

Window Functions

A SQL window function performs a group-aware calculation while retaining the original rows.

ConceptWorked examplePracticeKnowledge check
Textbook walkthrough

Window Functions

A SQL window function performs a group-aware calculation while retaining the original rows. Unlike GROUP BY, it does not collapse each partition to one row. The OVER clause defines the partition and ordering, enabling rankings, running totals, lags, moving summaries and group-level values alongside row detail.

Learning goal: explain why Window Functions behaves this way, apply it to a small example, and verify the result independently. Begin by being able to justify this first step: Keep the desired row grain unchanged.

Deeper walkthrough

Read Window Functions as a mechanism, not a recipe

Treat this as a sequence of observable decisions rather than one opaque command. Stage 1: Keep the desired row grain unchanged. Stage 2: Choose the window function such as ROW_NUMBER, SUM, AVG, LAG or LEAD. Stage 3: Use PARTITION BY to define independent groups when needed. Final checkpoint: For moving/running calculations, define the frame explicitly and verify boundary rows.

Mechanism

Follow the transformation

Keep the desired row grain unchanged.

Choose the window function such as ROW_NUMBER, SUM, AVG, LAG or LEAD.

Use PARTITION BY to define independent groups when needed.

Evidence

Know what would convince you

  • Recompute one result from a handful of source rows or an independent formula.
  • Check row counts, group totals and units before interpreting differences.
Useful distinctionGROUP BY: Collapses rows to one row per group.
Visual demonstration of Window Functions
Visual demonstration: use the diagram to trace the main objects and state changes involved in Window Functions.
Click a stage to inspect what happens, what changes, and what should be checked before moving on.
Stage 1

Keep the desired row grain unchanged

Keep the desired row grain unchanged. For Window Functions, identify the exact state before this stage, the operation or rule applied here, and the observable state afterwards so the mechanism remains inspectable.

State focus: identify exactly what changed at this stage and what observable evidence confirms that change.
How it works

Trace the mechanism step by step

  1. Keep the desired row grain unchanged.
  2. Choose the window function such as ROW_NUMBER, SUM, AVG, LAG or LEAD.
  3. Use PARTITION BY to define independent groups when needed.
  4. Use ORDER BY inside OVER when sequence matters.
  5. For moving/running calculations, define the frame explicitly and verify boundary rows.
Worked demonstration

Rank within region

-- Step 1 — Choose the output fields/expressions that the query should return.
SELECT region, product, revenue,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank_in_region
-- Step 2 — Identify the source table or intermediate relation that supplies rows.
FROM sales;
Expected / illustrative result
Every product row remains present, but each receives a ranking calculated within its region.
Interpret the result.

For Window Functions, trace one source row through the query and explain why it appears, disappears, duplicates or receives its calculated value.

Distinctions & related ideas

Place the concept correctly

GROUP BYCollapses rows to one row per group.
Window functionKeeps rows and adds group/ordered calculations.
ORDER BY in OVERDefines sequence for rank/running/lag calculations.
Use deliberately

When it is appropriate

Use Window Functions when it answers a defined question in SQL for Analytics and its inputs/assumptions match the current data or program state.

Boundary conditions

When to stop or reconsider

Reconsider Window Functions when the required information is unavailable, the operation would violate a validation/data boundary, or a simpler operation answers the question more transparently.

Common mistakes

Failure modes to recognise

  • Changing the population/grain without noticing it.
  • Using an undefined denominator, time window, unit or category rule.
  • Presenting a number/plot without reconciling it to source counts or totals.
Verification

How to check the result

  • Recompute one result from a handful of source rows or an independent formula.
  • Check row counts, group totals and units before interpreting differences.
  • Change one source value and predict which reported value/mark should change.
Hands-on practice

Demonstrate understanding

Try this:

Construct a tiny example of Window Functions. First keep the desired row grain unchanged. Then choose the window function such as ROW_NUMBER, SUM, AVG, LAG or LEAD. Predict the result before execution and explain one boundary or failure case.

Use tiny tables where every row can be enumerated. State output grain first, then reconcile each output row to the source.
Knowledge check

Check reasoning, not memorisation

Which approach best demonstrates understanding of Window Functions?

Quick reference

Remember the logic

Step 1Keep the desired row grain unchanged.
Step 2Choose the window function such as ROW_NUMBER, SUM, AVG, LAG or LEAD.
Step 3Use PARTITION BY to define independent groups when needed.
Step 4Use ORDER BY inside OVER when sequence matters.
Lesson summary

What to remember

  • A SQL window function performs a group-aware calculation while retaining the original rows. Unlike GROUP BY, it does not collapse each partition to one row. The OVER clause defines the partition and ordering, enabling rankings, running totals, lags, moving summaries and group-level values alongside row detail.
  • Keep the desired row grain unchanged.
  • Changing the population/grain without noticing it.
  • Recompute one result from a handful of source rows or an independent formula.