Window functions in BigQuery

How BigQuery window functions pick their frame. With ORDER BY the default frame is RANGE up to the current row, which makes LAST_VALUE return the current row and gives tied rows the same running total. Covers ROWS vs RANGE, the named WINDOW clause, LAG and LEAD, and PERCENTILE_CONT.

A window function computes a value for each row from a set of related rows, without collapsing them the way GROUP BY does. In BigQuery the set of rows is the partition (PARTITION BY), their order is ORDER BY, and the slice around the current row is the frame. Most surprises come from the frame: adding ORDER BY to a window silently changes it, and LAST_VALUE with the default frame returns the current row.

Partition, order and the default frame

SUM(amount) OVER (PARTITION BY region) has no ORDER BY, so the frame is the whole partition and every row gets the same total. Add ORDER BY day and the same function becomes a running total:

WITH sales AS (
  SELECT 'north' AS region, 1 AS day, 10 AS amount UNION ALL
  SELECT 'north', 2, 20 UNION ALL
  SELECT 'north', 3, 5 UNION ALL
  SELECT 'south', 1, 7 UNION ALL
  SELECT 'south', 2, 3
)
SELECT
  region, day, amount,
  SUM(amount) OVER (PARTITION BY region) AS region_total,
  SUM(amount) OVER (PARTITION BY region ORDER BY day) AS running_total
FROM sales
ORDER BY region, day

The rule from Google’s docs: with ORDER BY and no frame clause, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Without ORDER BY it is the whole partition.

ROWS vs RANGE: ties are the difference

ROWS counts physical rows. RANGE works on the value of the ORDER BY key, so rows with the same key are peers and all of them are in the frame together. Because the default is RANGE, tied rows get the same running total:

WITH orders AS (
  SELECT 1 AS id, 1 AS day, 10 AS amount UNION ALL
  SELECT 2, 2, 20 UNION ALL
  SELECT 3, 2, 30 UNION ALL
  SELECT 4, 3, 40
)
SELECT
  id, day, amount,
  SUM(amount) OVER (ORDER BY day) AS default_frame,
  SUM(amount) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM orders
ORDER BY id

Orders 2 and 3 are on the same day. With the default frame both show 60, the total up to and including everything on day 2. With ROWS order 2 shows 30 and order 3 shows 60, and which of the two comes first is not defined unless the ORDER BY is unique. If you want a running total that moves one row at a time, write ROWS and make the order deterministic by adding a tiebreaker column.

RANGE can also take offsets, measured in key units rather than rows. Days 1, 2, 5 and 6 below have gaps, so “the last two days” and “the last two rows” differ on day 5:

WITH readings AS (
  SELECT 1 AS day, 10 AS temp UNION ALL
  SELECT 2, 12 UNION ALL
  SELECT 5, 20 UNION ALL
  SELECT 6, 22
)
SELECT
  day, temp,
  SUM(temp) OVER (ORDER BY day RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS last_2_days,
  SUM(temp) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS last_2_rows
FROM readings
ORDER BY day

An offset in RANGE needs exactly one ORDER BY key, and the key must be numeric. A DATE key is rejected:

WITH readings AS (
  SELECT DATE '2026-03-01' AS day, 10 AS temp UNION ALL
  SELECT DATE '2026-03-02', 12
)
SELECT day, SUM(temp) OVER (ORDER BY day RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS s
FROM readings

To get “the previous 7 days” on dates, order by UNIX_DATE(day) (days as an integer) and use RANGE BETWEEN 6 PRECEDING AND CURRENT ROW. The offset in ROWS or RANGE must be a literal or a query parameter. A column reference is an error (“Window framing expression must be a literal or parameter”).

Moving averages with ROWS

For “the last 3 rows”, use ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Near the start the frame is shorter, not padded, so the first average is over one row:

WITH daily AS (
  SELECT 1 AS day, 100 AS visits UNION ALL
  SELECT 2, 130 UNION ALL
  SELECT 3, 90 UNION ALL
  SELECT 4, 160 UNION ALL
  SELECT 5, 120
)
SELECT
  day, visits,
  AVG(visits) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3_days,
  COUNT(*) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rows_in_frame
FROM daily
ORDER BY day

If you want NULL until the frame is full, compare COUNT(*) OVER (same frame) with the frame size.

LAST_VALUE and the default frame

FIRST_VALUE, LAST_VALUE and NTH_VALUE look at the frame. With ORDER BY and no frame clause the frame ends at the current row, so LAST_VALUE returns the current row’s own value:

WITH logins AS (
  SELECT 'ana' AS user_name, 1 AS day UNION ALL
  SELECT 'ana', 4 UNION ALL
  SELECT 'ana', 9
)
SELECT
  user_name, day,
  LAST_VALUE(day) OVER (PARTITION BY user_name ORDER BY day) AS default_frame,
  LAST_VALUE(day) OVER (
    PARTITION BY user_name ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS whole_partition
FROM logins
ORDER BY day

The default_frame column repeats day. To get the last row of the partition, extend the frame with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or flip the order and use FIRST_VALUE.

IGNORE NULLS skips NULLs when picking the value. Together with the default frame it carries the last known value forward, which is the usual way to fill gaps:

WITH readings AS (
  SELECT 1 AS hour, 20.5 AS temp UNION ALL
  SELECT 2, NULL UNION ALL
  SELECT 3, NULL UNION ALL
  SELECT 4, 22.0
)
SELECT
  hour, temp,
  LAST_VALUE(temp) OVER (ORDER BY hour) AS last_value_default,
  LAST_VALUE(temp IGNORE NULLS) OVER (ORDER BY hour) AS carried_forward
FROM readings
ORDER BY hour

LAG, LEAD and ranking functions have no frame

LAG(value, offset, default) and LEAD read a row at a fixed distance, so they ignore frames. BigQuery rejects a frame clause on them instead of ignoring it, and the same goes for RANK, DENSE_RANK and ROW_NUMBER:

WITH t AS (SELECT 1 AS day, 10 AS v UNION ALL SELECT 2, 20)
SELECT LAG(v) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev
FROM t

Without a row to read, LAG and LEAD return NULL unless you pass the third argument:

WITH prices AS (
  SELECT 1 AS day, 10.0 AS price UNION ALL
  SELECT 2, 12.0 UNION ALL
  SELECT 3, 9.0
)
SELECT
  day, price,
  LAG(price) OVER (ORDER BY day) AS prev,
  LAG(price, 1, 0.0) OVER (ORDER BY day) AS prev_or_zero,
  LEAD(price, 2) OVER (ORDER BY day) AS two_ahead,
  price - LAG(price) OVER (ORDER BY day) AS change
FROM prices
ORDER BY day

The FIRST_VALUE, LAST_VALUE and NTH_VALUE functions do accept a frame clause. Google’s page on window function calls says only aggregate analytic functions can use one, which leaves them out, but a dry run of FIRST_VALUE(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) passes.

RANK leaves gaps after ties, DENSE_RANK doesn’t, and ROW_NUMBER numbers tied rows in an undefined order unless the ORDER BY breaks the tie:

WITH scores AS (
  SELECT 'ana' AS player, 90 AS score UNION ALL
  SELECT 'ben', 90 UNION ALL
  SELECT 'cy', 80 UNION ALL
  SELECT 'di', 70
)
SELECT
  player, score,
  RANK() OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank,
  ROW_NUMBER() OVER (ORDER BY score DESC, player) AS row_number
FROM scores
ORDER BY score DESC, player

Named windows

When several functions share a window, name it once with a WINDOW clause after HAVING and refer to it with OVER w. A function can add a frame to a named window only if the named window has none. If it has one, BigQuery rejects the reference (“cannot reference another window that contains or inherits a window frame clause”):

WITH sales AS (
  SELECT 'north' AS region, 1 AS day, 10 AS amount UNION ALL
  SELECT 'north', 2, 20 UNION ALL
  SELECT 'south', 1, 7 UNION ALL
  SELECT 'south', 2, 3
)
SELECT
  region, day,
  SUM(amount) OVER w AS running_total,
  SUM(amount) OVER (w ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS last_two,
  ROW_NUMBER() OVER w AS n
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY day)
ORDER BY region, day

PERCENTILE_CONT only exists as a window function

In BigQuery PERCENTILE_CONT and PERCENTILE_DISC are analytic functions only. There is no aggregate form for GROUP BY, so a call without OVER fails:

WITH latencies AS (SELECT 10 AS ms UNION ALL SELECT 20 UNION ALL SELECT 100)
SELECT PERCENTILE_CONT(ms, 0.5) AS median_ms
FROM latencies

With OVER (PARTITION BY ...) it works, and every row of the partition gets the same value, so use SELECT DISTINCT or QUALIFY ROW_NUMBER() ... = 1 if you want one row per group. PERCENTILE_CONT interpolates, so the median of 10, 20, 30, 100 is 25. PERCENTILE_DISC returns an actual value from the data, here 20. The percentile is the second argument, the value is the first, and the OVER clause can’t have its own ORDER BY:

WITH latencies AS (
  SELECT 'api' AS service, 10 AS ms UNION ALL
  SELECT 'api', 20 UNION ALL
  SELECT 'api', 30 UNION ALL
  SELECT 'api', 100 UNION ALL
  SELECT 'web', 5 UNION ALL
  SELECT 'web', 15
)
SELECT
  service, ms,
  PERCENTILE_CONT(ms, 0.5) OVER (PARTITION BY service) AS median_ms,
  PERCENTILE_DISC(ms, 0.5) OVER (PARTITION BY service) AS median_disc
FROM latencies
ORDER BY service, ms

If an approximate answer is enough and you want a plain GROUP BY, APPROX_QUANTILES is an aggregate function. See approximate aggregation.

Window functions can’t go in WHERE

WHERE runs before window functions are computed, so BigQuery rejects them there:

WITH orders AS (SELECT 'c1' AS customer, 5 AS amount UNION ALL SELECT 'c1', 9)
SELECT customer, amount
FROM orders
WHERE ROW_NUMBER() OVER (PARTITION BY customer ORDER BY amount DESC) = 1

Filter on a window function with QUALIFY, which runs after them. See QUALIFY. In Querylab.io, a subquery that only wraps ROW_NUMBER(), RANK() or DENSE_RANK() to filter on it gets a hint with the quick fix “Convert to QUALIFY clause” (Code quality).

Window functions over aggregates

Window functions run after GROUP BY, so they can take an aggregate as their argument. SUM(SUM(amount)) OVER () adds up the per-group sums, which gives each group’s share of the total in one pass:

WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount UNION ALL
  SELECT 'c1', 30 UNION ALL
  SELECT 'c2', 20 UNION ALL
  SELECT 'c3', 50
)
SELECT
  customer,
  SUM(amount) AS total,
  SUM(SUM(amount)) OVER () AS grand_total,
  ROUND(SUM(amount) / SUM(SUM(amount)) OVER (), 2) AS share
FROM orders
GROUP BY customer
ORDER BY customer

What it costs

Window functions don’t change how many bytes a query reads. Bytes are billed for the columns read, as described in how queries are billed. A window does need each partition sorted, so a very large partition is slow. OVER () with no PARTITION BY makes the whole table one partition.