Window functions

GROUP BY collapses rows into one per group. A window function keeps every row and adds a value worked out over a set of related rows, the window. OVER (…) is what makes a function a window function.

Inside OVER, PARTITION BY splits the rows into groups (like GROUP BY, but nothing collapses) and ORDER BY sets the order within each group. ROW_NUMBER() numbers the rows 1, 2, 3 in that order. Each customer's orders, numbered by date:

SELECT customer_id, id, ordered_on,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_on) AS nth
FROM orders
ORDER BY customer_id, nth;

Ties are where the numbering functions differ. ROW_NUMBER always gives 1, 2, 3, 4, so with a tie the order between the tied rows is arbitrary unless you add a tie-breaker. RANK gives tied rows the same number and then skips (1, 2, 2, 4). DENSE_RANK doesn't skip (1, 2, 2, 3).

WITH counts AS (
  SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id
)
SELECT customer_id, n,
  ROW_NUMBER() OVER (ORDER BY n DESC, customer_id) AS row_num,
  RANK() OVER (ORDER BY n DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY n DESC) AS dense
FROM counts
ORDER BY n DESC, customer_id;

LAG(x) reads x from the row before in the window, and LEAD(x) from the row after; the first row's LAG is NULL. Good for "change since last time" and "days between orders":

SELECT customer_id, ordered_on,
  LAG(ordered_on) OVER (PARTITION BY customer_id ORDER BY ordered_on) AS previous
FROM orders
WHERE customer_id IN (1, 2)
ORDER BY customer_id, ordered_on;

An aggregate with OVER (ORDER BY …) gives a running total: each row adds up everything up to and including itself. (With ties in the ORDER BY the default frame treats tied rows as one step; write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to go strictly row by row.)

WITH totals AS (
  SELECT o.id, o.ordered_on, SUM(i.qty * p.price) AS total
  FROM orders o
  JOIN order_items i ON i.order_id = o.id
  JOIN products p ON p.id = i.product_id
  WHERE o.status = 'shipped'
  GROUP BY o.id, o.ordered_on
)
SELECT ordered_on, total,
  SUM(total) OVER (ORDER BY ordered_on) AS running_total
FROM totals
ORDER BY ordered_on;

Window functions are worked out after WHERE, GROUP BY and HAVING, so you can't filter on one in WHERE. Put the query in a CTE and filter outside it. Some warehouses (Snowflake, BigQuery, Databricks and DuckDB among them) add a QUALIFY clause for exactly this.