BigQuery Window Functions: How to Rank, Run Totals, and Compare Rows

A BigQuery window function computes a value across a set of rows related to the current row, without collapsing those rows the way GROUP BY does. You write it as a normal function followed by an OVER (...) clause that defines the window: which rows to include, how to partition them, and what order to use. That is how you add a running total, a rank, or a "compared to the group average" column while keeping every original row in the output. Below is the syntax, the PARTITION BY and ORDER BY parts, the common functions, and how to filter on a window result.

The OVER clause syntax

Every window function has the same shape: the function, then OVER, then up to three optional pieces inside the parentheses.

function() OVER (
  PARTITION BY col        -- optional: split rows into groups
  ORDER BY col            -- optional: order within each group
  ROWS BETWEEN ...        -- optional: the frame, a sliding range of rows
)

Leave everything out and the window is the whole result set. Add PARTITION BY and the function restarts for each group. Add ORDER BY and rows are processed in that order, which is required for ranking and running totals. The frame clause narrows the window to a moving range, like the last three rows.

PARTITION BY: per-group calculations

PARTITION BY is the window equivalent of GROUP BY, except it does not remove rows. This query keeps every order and adds each customer's average order value beside it.

SELECT
  order_id,
  customer_id,
  total,
  AVG(total) OVER (PARTITION BY customer_id) AS customer_avg
FROM sales.orders

Now you can compare each order to that customer's own average in the same row, something a plain GROUP BY cannot do because it would collapse the orders into one row per customer. This is the single most useful thing window functions add: row-level detail and group-level context together.

Ranking: ROW_NUMBER, RANK, DENSE_RANK

The ranking functions number rows within each partition, in the order you specify. They differ only in how they handle ties.

SELECT
  region,
  rep_name,
  sales,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales DESC) AS rank_in_region
FROM sales.reps

Use ROW_NUMBER when you need one winner per group, for example the single top rep per region or the latest row per user for deduplication. When you then want to keep only rank 1, do not wrap it in a subquery; BigQuery's QUALIFY clause filters window results directly, which our note on filtering window functions with QUALIFY walks through.

Running totals and moving averages with a frame

Add ORDER BY and a frame clause and you get cumulative and sliding calculations. A running total sums from the start of the partition to the current row; a moving average sums a fixed window of recent rows.

SELECT
  day,
  revenue,
  SUM(revenue) OVER (ORDER BY day) AS running_total,
  AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7day
FROM finance.daily_revenue
ORDER BY day

The ROWS BETWEEN 6 PRECEDING AND CURRENT ROW frame makes a rolling seven-day average, the kind of smoothing you see on a stock research chart to strip out day-to-day noise. Change the number to widen or tighten the window. Without the ORDER BY, a running total has no meaning, because there is no sequence to accumulate along.

Comparing to the previous row: LAG and LEAD

LAG reaches back to an earlier row and LEAD reaches forward, which is how you calculate change over time without a self-join.

SELECT
  month,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM finance.monthly_revenue
ORDER BY month

LAG(revenue) returns the prior month's value in the current row, so subtracting gives month-over-month change. The first row has no previous value, so it returns NULL unless you supply a default with LAG(revenue, 1, 0).

Why you cannot use a window function in WHERE

Window functions run after WHERE and GROUP BY, so a rank does not exist yet when WHERE is evaluated. This fails:

-- error: window functions are not allowed in WHERE
WHERE ROW_NUMBER() OVER (...) = 1

The old fix was a subquery that computes the window in an inner SELECT and filters in the outer one. BigQuery's QUALIFY clause does the same job in one statement, so reach for it whenever you need to filter on a rank, a running total, or any other windowed value.

Ask for it in plain English instead

Window functions are powerful and easy to get subtly wrong: the partition, the order, the frame, and the tie behavior all have to line up. Agentsql connects read-only to BigQuery, turns a plain-English question like "seven-day moving average of revenue" or "top rep in each region" into the correct windowed SQL, runs it, and shows you the query so you can check the frame yourself. See the BigQuery integration and the SQL query generator, then see how it works and ask BigQuery a question in plain English.