SQL window functions: why does GROUP BY reject your column?

A window function computes a value per row over a group of related rows, without collapsing anything. That is the entire concept: GROUP BY answers "one number per group", windows answer "every row, with its group context attached".

The anatomy

SUM(amount) OVER (
  PARTITION BY city    -- restart per group
  ORDER BY order_date  -- running order
)   -- one value PER ROW, no row collapses

The ranking trio on a tie

RANK skips after ties (1, 2, 2, 4). DENSE_RANK does not (1, 2, 2, 3). ROW_NUMBER never ties.

Look around: LAG and LEAD

LAG(amount)  OVER (ORDER BY order_date)   -- previous row
LEAD(amount) OVER (ORDER BY order_date)   -- next row

Deltas between periods, retention checks, sessionization: all LAG/LEAD shapes.

Running total

SUM(amount) OVER (ORDER BY order_date) AS running_total

Top-N per group: the interview pattern

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY city
      ORDER BY amount DESC
    ) AS rn
  FROM sales
)
SELECT * FROM ranked WHERE rn <= 3;

Top 3 sales per city, latest event per user, best product per category: all this exact shape. Learn it once.

The trap: a window in WHERE

-- wrong
WHERE ROW_NUMBER() OVER (...) <= 3

-- right: name it in a CTE, filter outside
WHERE rn <= 3

Window functions run after WHERE (same execution-order story as the alias error). BigQuery, Snowflake and DuckDB offer QUALIFY as the shortcut; the CTE works everywhere.