Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 37 ยท Querying and Functions

Window Functions and QUALIFY in BigQuery

Learn BigQuery window (analytic) functions: OVER, PARTITION BY, ORDER BY, frames, ROW_NUMBER, RANK, LAG, LEAD, running totals, moving averages, and the QUALIFY clause for filtering them.

A window function computes a value for each row using a set of related rows (its window), without collapsing rows the way GROUP BY does. They answer questions such as "rank every product within its category", "running total of daily revenue" or "how much did this month change from last month?" They are a staple of analytical SQL, and BigQuery adds a handy clause, QUALIFY, to filter on them directly.

Window functions are standard SQL, so every example here runs on our stand-in and works in BigQuery with identical syntax. The only BigQuery-only piece is QUALIFY, which is shown read-only with its portable equivalent beside it.

The OVER clause

TEXT
function(...) OVER (
  PARTITION BY group_columns
  ORDER BY sort_columns
  frame
)
  • PARTITION BY splits rows into independent groups (omit it for one big group).
  • ORDER BY orders rows inside each partition.
  • The frame (ROWS BETWEEN ...) chooses which rows around the current one are included.

Aggregates as windows

Put OVER after an ordinary aggregate and every row keeps its identity while seeing the group total:

SQL Stand-in: share of category total ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Ranking: ROW_NUMBER, RANK, DENSE_RANK

Function Ties Example for scores 90, 90, 80
ROW_NUMBER() Broken arbitrarily 1, 2, 3
RANK() Same rank, then a gap 1, 1, 3
DENSE_RANK() Same rank, no gap 1, 1, 2
NTILE(n) Splits into n buckets โ€”
SQL Stand-in: rank products inside each category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Top N per group with QUALIFY

The classic request: "the 2 most expensive products in each category". You cannot put a window function in WHERE, because WHERE runs before windows are calculated. The portable answer is a subquery:

SQL Stand-in: top 2 per category (subquery) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

BigQuery's QUALIFY filters after window functions, just as HAVING filters after aggregates, so no subquery is needed (read-only, BigQuery syntax):

SQL
SELECT category_id, name, price
FROM `my-project.shop.products`
QUALIFY ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) <= 2
ORDER BY category_id, price DESC;

The evaluation order is: FROM, WHERE, GROUP BY, HAVING, window functions, QUALIFY, DISTINCT, ORDER BY, LIMIT.

A second very common use is deduplicating to the latest row per key:

SQL
SELECT *
FROM `my-project.crm.contacts`
WHERE TRUE
QUALIFY ROW_NUMBER() OVER (PARTITION BY contact_id ORDER BY updated_at DESC) = 1;

(BigQuery requires a WHERE, GROUP BY or HAVING clause alongside QUALIFY; WHERE TRUE is the idiom when you have none.)

LAG and LEAD

Look at the previous or next row inside the partition:

SQL Stand-in: change since the previous order ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

LAG(x, 1, 0) supplies 0 for the first row, where there is no previous one.

Running totals and moving averages

Add an ORDER BY and an aggregate becomes cumulative. Add a frame and you get moving windows:

SQL Stand-in: running total and 3-row moving average ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Frames use either ROWS (a count of rows) or RANGE (a value range on the ordering column). With only ORDER BY, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats ties as peers. When you mean physical rows, write ROWS explicitly.

FIRST_VALUE, LAST_VALUE, NTH_VALUE and named windows

FIRST_VALUE(x) returns the first row's value in the frame. LAST_VALUE needs a full frame (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) or it only reaches the current row. When several functions share a window, name it once with WINDOW:

SQL
SELECT
  name, price,
  RANK() OVER w AS price_rank,
  SUM(price) OVER w AS running_price
FROM `my-project.shop.products`
WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);

Performance notes

Every window needs its rows sorted by the partition and order columns, which means a shuffle. A partition that is huge (one value covers most rows) concentrates work on a single slot and can fail with a "resources exceeded" error. Keep partitions reasonable, and filter rows before the window when you can.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main difference between a window function and GROUP BY?

    A window function adds a computed column per row based on related rows.
  2. 2. For scores 90, 90, 80, what does DENSE_RANK() give?

    DENSE_RANK gives tied rows the same rank and does not skip numbers.
  3. 3. What does QUALIFY do in BigQuery?

    QUALIFY is to window functions what HAVING is to aggregates.
  4. 4. Which frame gives a moving average of the current and two previous rows?

    That frame covers exactly three rows ending at the current one.

Summary

  • OVER (PARTITION BY ... ORDER BY ... frame) turns functions into windows.
  • ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and running aggregates cover most needs.
  • QUALIFY filters on window results and makes top-N-per-group and dedup queries short.
  • State frames explicitly with ROWS; beware of giant partitions.

Next: Joins, CTEs and Scripting