Visualize how calculations are performed across a set of table rows that are somehow related to the current row.
GROUP BY combines rows into one row per group. A window function instead calculates across a window of related rows and returns a value for each original row. This makes it useful for ranks, running totals, moving averages, and comparing a row with its neighbors.
PARTITION BY divides rows into independent groups; ORDER BY defines their sequence inside each group. The window does not remove rows.
ROW_NUMBER assigns a unique sequence within a partition. RANK leaves gaps after ties; DENSE_RANK does not. LAG and LEAD read a value from a previous or next row in the ordered partition. Include a deterministic tie-breaker when the order must be stable.
A window frame defines which ordered rows contribute to the current calculation. Defaults depend on the function and ordering and can include peers with equal order values. For a running total, specify a frame deliberately when peer behavior matters. For LAST_VALUE over the entire partition, a frame ending at the current row often surprises; request UNBOUNDED FOLLOWING when you mean the partition's final value.
Window functions run after WHERE, grouping, and HAVING in the logical query model. To filter by a window result, put the calculation in a subquery or CTE and filter outside it. See query execution order.
With PARTITION BY customer_id and an order, a cumulative frame produces a running total that changes row by row. To show each order alongside the customer's full total, use the same partition but omit the ordering, or specify a frame that covers the whole partition. Ranking has no frame requirement in the same way, but ties still matter. Verify a partition with duplicate sort values, a single row, and NULLs so the result matches the intended report rather than a default frame assumption.
A window result is calculated after WHERE, so this form cannot generally filter rows by a rank alias in the same query block. Put the rank in a CTE or subquery, then filter the outer result to keep the top rows per partition. Include a tie policy: ROW_NUMBER selects a fixed number of rows, while RANK may return more rows when ranks tie. The choice changes whether “top three” means exactly three rows or every row in the top three rank positions.
About to run: switch Load Dataset to Weather Data. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What does this module say about “Aggregate without collapsing rows”?
What does this module say about “Ranking and neighboring rows”?
What does this module say about “Frames can change the answer”?
Rank, total and compare across rows without collapsing them, using OVER and PARTITION BY on a live result set.