BigQuery QUALIFY Clause: Filter Window Functions Directly — sqlfmt

sqlfmt▍

BigQuery QUALIFY Clause: Filter Window Functions Directly — sqlfmt

Key takeaways

QUALIFY filters a Boolean condition after window functions.
It fits top-N-per-group and latest-row-per-key queries.
WHERE filters input rows; QUALIFY filters window results.

What does QUALIFY do?

QUALIFY filters the output of a window function without an outer query. It makes a top-N-per-group query compact while preserving the evaluation stage in the SQL itself.

Keep the three best-selling products per category

SELECT category, product, purchases,
       RANK() OVER (PARTITION BY category ORDER BY purchases DESC) AS purchase_rank
FROM Produce
QUALIFY purchase_rank <= 3;

Google documents that QUALIFY filters window-function results and is evaluated after WINDOW but before DISTINCT, ORDER BY, and LIMIT.

WHERE versus QUALIFY

Use WHERE to reduce input rows, such as by date. Use QUALIFY for a window result, such as row_number = 1. Combining both expresses two different filtering stages.

Latest event per user

SELECT user_id, event_name, occurred_at,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY occurred_at DESC) AS event_number
FROM events
WHERE occurred_at >= TIMESTAMP('2026-01-01')
QUALIFY event_number = 1;

RANK or ROW_NUMBER?

ROW_NUMBER gives each row a unique sequence, so row_number = 1 returns one row per partition. RANK gives ties the same rank, so rank <= 3 can return more than three rows.

How portable is QUALIFY?

Not every SQL dialect supports QUALIFY. Use a CTE or subquery to calculate the window value and an outer WHERE to filter it when portability is required.

Learn the general distinction in window functions versus GROUP BY.

Frequently asked questions

Can QUALIFY use a SELECT alias?

Yes. BigQuery examples use a window-function alias in QUALIFY, and the function can also appear directly in the condition.

Can QUALIFY work without a window function?

No. BigQuery requires a window function in QUALIFY or the SELECT list. Use WHERE or HAVING for conditions unrelated to a window calculation.