Window functions explained visually

A window function computes a value across a set of rows related to the current row, without collapsing them like GROUP BY does. Three knobs: PARTITION BY (which rows count), ORDER BY (the sequence), and frame (how far back/forward).

PARTITION BY = group, but keep all rows

PARTITION BY customer_id splits the input into per-customer windows. Unlike GROUP BY, you still get every row out.

ORDER BY inside OVER = the sequence within the window

Required for ROW_NUMBER and any cumulative frame. Without it, frames default to all rows in the partition.

ROW_NUMBER vs RANK vs DENSE_RANK

• ROW_NUMBER: 1, 2, 3, 4 — ties broken arbitrarily.
• RANK: 1, 2, 2, 4 — ties share a rank, next rank skips.
• DENSE_RANK: 1, 2, 2, 3 — ties share, next rank does not skip.

SELECT
  customer_id,
  ordered_at,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS visit_number
FROM orders;

Lineage tools track window expressions as derived projections of their input columns — see the running-total example for a worked walkthrough.