Window Functions in BigQuery: 10 Code-Along Examples

The window functions guide draws the line that defines the whole topic: a window function performs a calculation across a set of rows and returns a value for each row, without collapsing anything. GROUP BY gives you one summary row per group and throws the detail away; a window function keeps every row and attaches the summary alongside it. That single difference is what makes running totals, rankings, and period-over-period comparisons possible, none of which GROUP BYcan express. This workbook builds the skill from a first OVER clause to moving averages and quartiles, one function family at a time.

Setup: run this once

-- create a scratch dataset if you do not have one
CREATE SCHEMA IF NOT EXISTS scratch;
-- a small purchases table: several customers, categories, and dates
CREATE OR REPLACE TABLE scratch.purchases AS
SELECT * FROM UNNEST([
  STRUCT(1 AS purchase_id, 101 AS customer_id, 'Books'   AS category, 40  AS amount, DATE '2026-01-01' AS purchase_date, 'complete' AS status),
  (2,  101, 'Books',   55,  DATE '2026-01-03', 'complete'),
  (3,  101, 'Games',   90,  DATE '2026-01-06', 'complete'),
  (4,  102, 'Books',   30,  DATE '2026-01-02', 'complete'),
  (5,  102, 'Games',  120,  DATE '2026-01-04', 'complete'),
  (6,  102, 'Games',   75,  DATE '2026-01-09', 'refunded'),
  (7,  103, 'Music',   20,  DATE '2026-01-01', 'complete'),
  (8,  103, 'Music',   60,  DATE '2026-01-05', 'complete'),
  (9,  103, 'Books',   45,  DATE '2026-01-07', 'complete'),
  (10, 104, 'Games',  200,  DATE '2026-01-08', 'complete'),
  (11, 104, 'Music',   35,  DATE '2026-01-10', 'complete'),
  (12, 104, 'Books',   80,  DATE '2026-01-11', 'complete')
]);

The table has twelve purchases across four customers and three categories, with dates spread through January, which is enough to demonstrate every function below. Each example is a standalone query you can paste and run.

1. The OVER clause: aggregate without collapsing

The empty OVER () is the smallest window function there is. It computes an aggregate across all rows and pins the result to every row, so you can compare each purchase against the overall average without a GROUP BY and without a self-join.

SELECT
  purchase_id,
  amount,
  AVG(amount) OVER () AS overall_avg,
  amount - AVG(amount) OVER () AS diff_from_avg
FROM scratch.purchases
ORDER BY purchase_id;

The OVER () is what turns AVG from a collapsing aggregate into a window function. With GROUP BY you would get one row holding the average and lose the individual purchases; here every one of the twelve rows survives, each carrying the same overall average beside it and its own distance from that average. That per-row comparison against a whole-table figure is the simplest thing windows do that plain aggregation cannot, and every later example is a refinement of this move.

2. PARTITION BY: restart the calculation per group

PARTITION BY divides the rows into independent groups and computes the window separately within each, without collapsing them. It is “group by, but keep the rows”.

SELECT
  purchase_id,
  category,
  amount,
  AVG(amount) OVER (PARTITION BY category) AS category_avg,
  COUNT(*)    OVER (PARTITION BY category) AS purchases_in_category
FROM scratch.purchases
ORDER BY category, purchase_id;

Now the average resets at each category boundary, so a Books purchase is compared against the Books average and a Games purchase against the Games average, all while every individual row remains visible. The COUNT(*) OVER (PARTITION BY category) on the same line shows you can attach several partitioned aggregates at once. This is the pattern to reach for whenever you want a group summary sitting next to the detail rather than replacing it, which is a report requirement more often than not.

3. Running totals with SUM OVER (ORDER BY)

Add ORDER BY inside the window and the aggregate becomes cumulative. Ordered by date, SUM adds up everything from the first row to the current one, which is a running total.

SELECT
  purchase_date,
  amount,
  SUM(amount) OVER (ORDER BY purchase_date) AS running_total
FROM scratch.purchases
ORDER BY purchase_date;

The ORDER BY inside OVER is what changes the meaning entirely. Without it the window is the whole partition and SUMreturns the grand total on every row; with it, the window becomes “all rows up to this one in date order”, so the total grows down the page. Running totals are the classic window use case and the reason ORDER BY exists inside a window: it defines the sequence along which the accumulation happens. Note that this orders across the whole table, so purchases sharing a date are summed together at that step.

4. Running totals per group

Combine the two clauses and you get a running total that restarts for each group. PARTITION BY sets where the accumulation resets, and ORDER BY sets the sequence within it.

SELECT
  category,
  purchase_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY category
    ORDER BY purchase_date
  ) AS running_total_by_category
FROM scratch.purchases
ORDER BY category, purchase_date;

Reading the two clauses together is the key skill. PARTITION BY category says “start a fresh running total for each category”, and ORDER BY purchase_date says “accumulate in date order within it”, so the total climbs through the Books rows, resets to zero at the first Games row, and climbs again. Almost every real running-total report is partitioned like this, since you rarely want one cumulative line across unrelated groups; you want a separate one per customer, per product, or per region.

5. Ranking: ROW_NUMBER, RANK, and DENSE_RANK

Three ranking functions look similar and differ precisely on how they handle ties. Computing all three side by side is the fastest way to internalise the difference.

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM scratch.purchases
  GROUP BY customer_id
)
SELECT
  customer_id,
  total_spent,
  ROW_NUMBER() OVER (ORDER BY total_spent DESC) AS row_num,
  RANK()       OVER (ORDER BY total_spent DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY total_spent DESC) AS dense_rank
FROM customer_totals
ORDER BY total_spent DESC;

The three answer three different questions about ties. ROW_NUMBER always gives a unique number even to tied values, breaking ties arbitrarily. RANK gives tied rows the same rank and then skips the next numbers, so two rows at rank 1 are followed by rank 3. DENSE_RANK also ties but does not skip, so it goes 1, 1, 2. The rule of thumb: ROW_NUMBER when you need exactly one row per position, RANK when ties should leave gaps as in sport, and DENSE_RANK when you want contiguous rank labels. Notice the window function sits on top of a GROUP BY in the CTE, which is a common two-step shape.

6. Top N per group with ROW_NUMBER

The most useful ranking pattern in practice: number the rows within each group, then filter. You cannot filter on a window function in the same WHERE where it is computed, so it goes in a subquery.

SELECT *
FROM (
  SELECT
    customer_id,
    category,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY amount DESC
    ) AS rank_in_customer
  FROM scratch.purchases
)
WHERE rank_in_customer <= 2
ORDER BY customer_id, rank_in_customer;

This gives each customer’s two biggest purchases. The two-layer structure is mandatory and worth understanding: window functions are computed after WHERE, so the rank does not exist yet when the outer filter runs, which is why the ranking happens in the inner query and the WHERE rank_in_customer <= 2 sits in the outer one. “Top N per group” is one of the questions GROUP BY genuinely cannot answer, and this ROW_NUMBER-then-filter pattern is the standard solution across every SQL dialect.

7. LAG and LEAD: compare against neighbouring rows

LAG reaches backward to a previous row and LEAD reaches forward, which turns “compare each day to the day before” into one line instead of a self-join.

WITH daily AS (
  SELECT purchase_date, SUM(amount) AS daily_sales
  FROM scratch.purchases
  GROUP BY purchase_date
)
SELECT
  purchase_date,
  daily_sales,
  LAG(daily_sales)  OVER (ORDER BY purchase_date) AS prev_day,
  daily_sales - LAG(daily_sales) OVER (ORDER BY purchase_date) AS day_change,
  LEAD(daily_sales) OVER (ORDER BY purchase_date) AS next_day
FROM daily
ORDER BY purchase_date;

LAG fetches the value from the previous row in date order, so subtracting it from the current row gives the day-over-day change directly. The first row has no previous row, so LAG returns NULL there, which is correct rather than an error, and you can supply a default with a second argument like LAG(daily_sales, 1, 0) if you prefer zero. LEAD is the mirror image, looking ahead, which is useful for “how much did the next period grow”. Period-over-period comparison is the second headline use of windows, and it is entirely built on these two functions.

8. FIRST_VALUE and LAST_VALUE, and the frame gotcha

FIRST_VALUE and LAST_VALUE fetch the value at the start or end of a window, but LAST_VALUE hides the single most common window-function trap, which this example exposes and fixes.

SELECT
  customer_id,
  purchase_date,
  amount,
  FIRST_VALUE(amount) OVER (
    PARTITION BY customer_id ORDER BY purchase_date
  ) AS first_purchase,
  LAST_VALUE(amount) OVER (
    PARTITION BY customer_id ORDER BY purchase_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_purchase
FROM scratch.purchases
ORDER BY customer_id, purchase_date;

FIRST_VALUE works as expected because the default window frame already includes the first row. LAST_VALUE does not, and here is why: when you add ORDER BY, the default frame runs from the start of the partition to the current row, so the “last value” is only ever the current row, which is useless. The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING widens the frame to the entire partition, and only then does LAST_VALUE return the genuine final purchase. This is the gotcha the guide flags, and forgetting it produces a last_purchase column that quietly equals amount on every row.

9. Moving averages with an explicit frame

The frame clause you just used to fix LAST_VALUE is a tool in its own right. Restricting the window to a sliding band of rows around the current one produces a moving average, the standard smoother for noisy time series.

WITH daily AS (
  SELECT purchase_date, SUM(amount) AS daily_sales
  FROM scratch.purchases
  GROUP BY purchase_date
)
SELECT
  purchase_date,
  daily_sales,
  AVG(daily_sales) OVER (
    ORDER BY purchase_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3day
FROM daily
ORDER BY purchase_date;

The frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW defines a window of the current day plus the two before it, so AVG computes a trailing three-day average that slides down the table one row at a time. Change the 2 and you change the smoothing window; use 1 PRECEDING AND 1 FOLLOWING for a centred average instead of a trailing one. The frame is what gives windows their fine control: PARTITION BY chooses the group, ORDER BY chooses the sequence, and ROWS BETWEENchooses exactly how many neighbours enter each calculation.

10. NTILE: split rows into buckets

NTILE(n) divides the ordered rows into n roughly equal groups and labels each row with its bucket number, which is how you form quartiles, deciles, or any even split for segmentation.

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM scratch.purchases
  GROUP BY customer_id
)
SELECT
  customer_id,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent DESC) AS spending_quartile
FROM customer_totals
ORDER BY total_spent DESC;

NTILE(4) sorts the customers by spend and splits them into four bands, so quartile 1 holds the biggest spenders and quartile 4 the smallest. When the row count does not divide evenly, the earlier buckets take one extra row each, which is the sensible default. This is the engine behind customer segmentation, “top 10 percent” flags, and any analysis that needs even-sized tiers rather than fixed thresholds, and it is a single window function where the hand-rolled alternative would be a tangle of percentiles and case statements.

Work through these and you have the whole article in practice: the bare OVER clause, PARTITION BY, running totals with an ordered SUM, partitioned running totals, all three ranking functions and their tie behaviour, top-N-per-group with the ROW_NUMBER-then-filter pattern, LAG and LEAD for period comparisons, FIRST_VALUE and LAST_VALUE with the frame fix, moving averages, and NTILE for bucketing. The unifying idea the guide leaves you with is the one to keep: window functions add analysis to rows without removing them, so whenever a report needs both the detail and a calculation across it, reach for OVER before you reach for GROUP BY.

See you soon.