# 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.

Source: https://docs.querylab.io/bigquery/window-functions/

Updated: 2026-10-14

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:

**region total vs running total**

```sql
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
```

Result in BigQuery (5 rows):

| region | day | amount | region_total | running_total |
| --- | --- | --- | --- | --- |
| north | 1 | 10 | 35 | 10 |
| north | 2 | 20 | 35 | 30 |
| north | 3 | 5 | 35 | 35 |
| south | 1 | 7 | 10 | 7 |
| south | 2 | 3 | 10 | 10 |

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:

**the default frame vs ROWS on a tie**

```sql
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
```

Result in BigQuery (4 rows):

| id | day | amount | default_frame | rows_frame |
| --- | --- | --- | --- | --- |
| 1 | 1 | 10 | 10 | 10 |
| 2 | 2 | 20 | 60 | 30 |
| 3 | 2 | 30 | 60 | 60 |
| 4 | 3 | 40 | 100 | 100 |

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:

**RANGE 1 PRECEDING vs ROWS 1 PRECEDING**

```sql
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
```

Result in BigQuery (4 rows):

| day | temp | last_2_days | last_2_rows |
| --- | --- | --- | --- |
| 1 | 10 | 10 | 10 |
| 2 | 12 | 22 | 22 |
| 5 | 20 | 20 | 32 |
| 6 | 22 | 42 | 42 |

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

**RANGE with an offset on a DATE**

```sql
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
```

BigQuery rejects this query:

```text
ORDER BY key must be numeric in a RANGE-based window with OFFSET PRECEDING or OFFSET FOLLOWING boundaries, but has type DATE at [5:29]
```

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:

**3-row moving average**

```sql
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
```

Result in BigQuery (5 rows):

| day | visits | avg_3_days | rows_in_frame |
| --- | --- | --- | --- |
| 1 | 100 | 100.0 | 1 |
| 2 | 130 | 115.0 | 2 |
| 3 | 90 | 106.66666666666667 | 3 |
| 4 | 160 | 126.66666666666667 | 3 |
| 5 | 120 | 123.33333333333333 | 3 |

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:

**LAST_VALUE with and without a full frame**

```sql
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
```

Result in BigQuery (3 rows):

| user_name | day | default_frame | whole_partition |
| --- | --- | --- | --- |
| ana | 1 | 1 | 9 |
| ana | 4 | 4 | 9 |
| ana | 9 | 9 | 9 |

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:

**fill NULLs with the last known value**

```sql
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
```

Result in BigQuery (4 rows):

| hour | temp | last_value_default | carried_forward |
| --- | --- | --- | --- |
| 1 | 20.5 | 20.5 | 20.5 |
| 2 | NULL | NULL | 20.5 |
| 3 | NULL | NULL | 20.5 |
| 4 | 22.0 | 22.0 | 22.0 |

## LAG, LEAD and ranking take 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`:

**a frame clause on LAG**

```sql
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
```

BigQuery rejects this query:

```text
Window framing clause is not allowed for analytic function lag at [2:34]
```

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

**LAG, LEAD and the default value**

```sql
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
```

Result in BigQuery (3 rows):

| day | price | prev | prev_or_zero | two_ahead | change |
| --- | --- | --- | --- | --- | --- |
| 1 | 10.0 | NULL | 0.0 | 9.0 | NULL |
| 2 | 12.0 | 10.0 | 10.0 | NULL | 2.0 |
| 3 | 9.0 | 12.0 | 12.0 | NULL | -3.0 |

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:

**RANK, DENSE_RANK and ROW_NUMBER on a tie**

```sql
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
```

Result in BigQuery (4 rows):

| player | score | rank | dense_rank | row_number |
| --- | --- | --- | --- | --- |
| ana | 90 | 1 | 1 | 1 |
| ben | 90 | 1 | 1 | 2 |
| cy | 80 | 3 | 2 | 3 |
| di | 70 | 4 | 3 | 4 |

## 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"):

**one WINDOW clause, three functions**

```sql
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
```

Result in BigQuery (4 rows):

| region | day | running_total | last_two | n |
| --- | --- | --- | --- | --- |
| north | 1 | 10 | 10 | 1 |
| north | 2 | 30 | 30 | 2 |
| south | 1 | 7 | 7 | 1 |
| south | 2 | 10 | 10 | 2 |

## PERCENTILE_CONT is window-only

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:

**PERCENTILE_CONT without OVER**

```sql
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
```

BigQuery rejects this query:

```text
percentile_cont aggregate function is not supported.
```

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`:

**interpolated vs discrete median**

```sql
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
```

Result in BigQuery (6 rows):

| service | ms | median_ms | median_disc |
| --- | --- | --- | --- |
| api | 10 | 25.0 | 20 |
| api | 20 | 25.0 | 20 |
| api | 30 | 25.0 | 20 |
| api | 100 | 25.0 | 20 |
| web | 5 | 10.0 | 5 |
| web | 15 | 10.0 | 5 |

If an approximate answer is enough and you want a plain `GROUP BY`, `APPROX_QUANTILES` is an aggregate function. See [approximate aggregation](https://docs.querylab.io/bigquery/approximate-aggregation/).

## Window functions can't go in WHERE

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

**ROW_NUMBER() in WHERE**

```sql
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
```

BigQuery rejects this query:

```text
Analytic function not allowed in WHERE clause at [4:7]
```

Filter on a window function with `QUALIFY`, which runs after them. See [QUALIFY](https://docs.querylab.io/bigquery/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](https://docs.querylab.io/writing-sql/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:

**share of total after GROUP BY**

```sql
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
```

Result in BigQuery (3 rows):

| customer | total | grand_total | share |
| --- | --- | --- | --- |
| c1 | 40 | 110 | 0.36 |
| c2 | 20 | 110 | 0.18 |
| c3 | 50 | 110 | 0.45 |

## 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](https://docs.querylab.io/bigquery/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.

See what frame a window function uses when you do not give one. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Window function calls](https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls)

- [Navigation functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions)

- [Aggregate analytic functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_analytic_functions)