How to Use Window Functions in BigQuery
Last updated July 26, 2026 · By the SaturnSQL team
Add OVER (PARTITION BY ... ORDER BY ...) to a function to compute per-row values without collapsing rows. QUALIFY lets you filter on the window result directly.
SELECT
order_id,
customer_id,
order_total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_total DESC) AS rank_in_customer
FROM orders;ROW_NUMBER vs RANK vs DENSE_RANK
ROW_NUMBER always gives unique, sequential numbers even for ties. RANK leaves gaps after ties (1, 2, 2, 4); DENSE_RANK does not (1, 2, 2, 3).
SELECT
customer_id,
order_total,
RANK() OVER (PARTITION BY customer_id ORDER BY order_total DESC) AS rnk
FROM orders;QUALIFY: BigQuery's shortcut around a subquery
Standard SQL cannot filter on a window function in the same SELECT's WHERE clause, so most databases force a wrapping subquery. BigQuery's QUALIFY clause filters directly on window results, no wrapper needed.
SELECT customer_id, order_id, order_total
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_total DESC) = 1;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free