T5.1Interactive artifact · P5 · Analytics / SQL

SQL window functions, visualised

An interactive explainer for the OVER clause. Pick ROW_NUMBER, RANK, LAG, SUM or AVG, shape the window with PARTITION BY, ORDER BY and a frame, and see exactly which rows feed each result.

Title blockT5.1
Type
Data tool
Drawn
Runs
In your browser, nothing to install
Tags
sql · data-engineering · interactive · developer-tool

Detail A · Reading the drawing

What it shows

A window function computes a value for every row from a set of related rows, its window, without collapsing them the way GROUP BY does. That's what makes it the tool for running totals, top-N per group, month-over-month deltas and moving averages.

The explainer runs a real window engine over a 12-row sales table in your browser. Hover a result and the rows in its frame light up, with the arithmetic written out. Every setting also produces the SQL you'd write, ready for DuckDB, Postgres or Snowflake.

Detail B · Procedure

How to use it

  1. Start from a preset: running total, top-n per group, month-over-month, moving average, or rank vs dense_rank.
  2. Change PARTITION BY and watch the window restart at each partition boundary (the heavy rule in the table).
  3. For aggregates, switch the frame between start → current, n preceding → current and the whole partition.
  4. Hover any row, or press walk through rows, to see which rows feed its value.
  5. Copy the generated SQL, or the prompt for Claude, from the takeaway panel.

Detail C · Glossary

Key concepts

PARTITION BY
Splits rows into independent windows. The function restarts in each one and never reads across a boundary.
ORDER BY (inside OVER)
Orders rows within each partition. Ranking and offset functions depend on it, and it sets the default frame.
Frame (ROWS BETWEEN …)
The slice of the partition an aggregate sees for the current row, e.g. UNBOUNDED PRECEDING AND CURRENT ROW for a running total.
ROW_NUMBER · RANK · DENSE_RANK
All three number rows in order and differ only on ties: distinct numbers, shared ranks with gaps (1, 2, 2, 4), or shared ranks without gaps (1, 2, 2, 3).
LAG · LEAD
Read a value n rows behind or ahead within the partition. Past the edge of the partition they return NULL.

Detail D · Cited by

Related writing

Posts that use this drawing as a worked example.

  • D5.2
    Sessionization with SQL window functions

    A stream of events isn't sessions until you decide where one visit ends and the next begins. You can do that in three window functions — no self-joins, no Python — and the whole thing hinges on the same frame-clause detail that trips people up on running totals.

  • D5.1
    SQL window functions: a practical guide

    Window functions are the single highest-leverage SQL feature for analytics work, and the frame clause is the part everyone skips — right up until their running total is silently wrong. Here's the whole thing, practically.

All artifacts →