BigQuery QUALIFY: How to Filter on Window Functions

BigQuery QUALIFY filters rows based on the result of a window function, so it does for window functions what WHERE does for columns and HAVING does for aggregates. The classic use is QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY ts DESC) = 1 to keep only the latest row per group, with no subquery or CTE. Below is the syntax, the deduplication and top-N patterns you will use most, how it compares to the old subquery approach, and the rules to remember.

Why QUALIFY exists

You cannot put a window function in a WHERE clause, because WHERE runs before window functions are computed. Before QUALIFY, the workaround was to compute the window function in a subquery or CTE and then filter on its output in an outer query. QUALIFY collapses that into one statement, which is easier to read and to maintain.

SELECT
  user_id,
  event_type,
  event_time
FROM app.events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id ORDER BY event_time DESC
) = 1

That returns exactly one row per user, the most recent event, and nothing else. It is the single most common QUALIFY pattern, usually described as "deduplicate" or "keep the latest record per group."

Deduplicating rows

Duplicate rows are a fact of life in raw event and import tables. ROW_NUMBER partitioned by the key that should be unique, ordered by a tiebreaker such as load time, gives each duplicate a number, and QUALIFY keeps only number one.

SELECT *
FROM staging.imported_orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY order_id ORDER BY ingested_at DESC
) = 1

Choose the ORDER BY deliberately, because it decides which copy survives. Ordering by ingested_at DESC keeps the freshest load; ordering ascending would keep the earliest. Deduplication is also where a lot of silent data-quality problems hide, so it is worth watching your tables for freshness and volume anomalies alongside the query rather than trusting the dedup blindly.

Top N per group

Swap ROW_NUMBER for RANK or use <= to keep more than one row per group. This is how you get the top three products per category or the two highest orders per customer.

SELECT
  category,
  product_id,
  revenue
FROM sales.product_revenue
QUALIFY RANK() OVER (
  PARTITION BY category ORDER BY revenue DESC
) <= 3

Use ROW_NUMBER when you want exactly N rows even if there are ties, and RANK or DENSE_RANK when tied values should all be kept. The difference matters when two products have identical revenue and you care whether both appear.

QUALIFY vs the subquery approach

The two produce the same result; QUALIFY is just less code. Here is the older equivalent of the deduplication query, which you will still see in a lot of existing SQL.

-- the pre-QUALIFY way
SELECT * EXCEPT(rn)
FROM (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY order_id ORDER BY ingested_at DESC
    ) AS rn
  FROM staging.imported_orders
)
WHERE rn = 1

Both are correct. QUALIFY is easier to read and to change later, so prefer it for new queries in BigQuery. The subquery form is still useful when you need the row number as an actual output column, not just a filter.

Rules to remember

• A window function must be present. QUALIFY only works when there is a window function, either in the SELECT list or inside the QUALIFY expression itself.
• It runs late. QUALIFY is evaluated after SELECT, WHERE, GROUP BY, and HAVING, so it sees the window function results.
• You can reference an aliased window column from the SELECT list in some cases, but writing the full window function inside QUALIFY always works and is clearest.
• It is BigQuery Standard SQL only. Legacy SQL does not support it, which is one more reason to use Standard SQL over Legacy SQL.

Or let plain English write it

Window functions and QUALIFY are exactly the kind of SQL that is easy to get subtly wrong. Agentsql connects read-only to BigQuery, turns a question like "the latest order per customer" or "top three products in each category" into the correct Standard SQL, runs it, and shows you the query so a teammate can verify the partition and ordering. See the BigQuery integration, learn how to give your team safe access to BigQuery, then see how it works.