What does a QUALIFY clause do in a top-N-per-group query, in dialects that offer it?

## What the clause is for SQL has one filter per processing stage: `WHERE` filters input rows, `HAVING` filters groups after aggregation. Window functions arrived without a filter of their own, which is why the top-N-per-group pattern needs a second query level — you must materialise the rank as a column before any predicate can reach it. `QUALIFY` fills that gap. It is a clause that filters rows on the results of window functions, evaluated after those functions have been computed. Conceptually it is to window functions what `HAVING` is to aggregates. ```sql -- Snowflake / BigQuery / Teradata / DuckDB style SELECT employee_id, name, department_id, salary FROM employees QUALIFY ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) <= 3; ``` One query level, no CTE, and the intent is on a single line. ## Where it sits in evaluation order In dialects that implement it, `QUALIFY` runs after `FROM`, `WHERE`, `GROUP BY` and `HAVING` — those have already produced the rows the windows are computed over — and before `ORDER BY` and any row-limiting clause. That placement matters: a `WHERE` predicate in the same statement is applied *before* ranking, so `WHERE status = 'ACTIVE' QUALIFY ROW_NUMBER() OVER (...) <= 3` means "rank only the active employees, keep three", not "rank everyone, keep three, then discard inactive ones". That is usually the behaviour you want, and it is the same behaviour the two-level rewrite gives when the filter lives in the inner query. ## Portability `QUALIFY` is not part of the ISO SQL standard. It began in Teradata and is supported by Snowflake, Google BigQuery and DuckDB, among other analytical engines; other products may or may not have added it, so check your engine's documentation rather than assuming. It is not accepted by PostgreSQL, MySQL, SQL Server, Oracle or SQLite. Writing it in a codebase that might move between engines buys brevity at the cost of a rewrite later. ## The portable rewrite The translation is mechanical in both directions. Take the window function out of `QUALIFY`, give it an alias in the select list of a CTE or derived table, and filter that alias with `WHERE` in an enclosing query: ```sql WITH ranked AS ( SELECT employee_id, name, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn FROM employees ) SELECT employee_id, name, department_id, salary FROM ranked WHERE rn <= 3; ``` One wrinkle: in the `QUALIFY` form the rank never appears in the output, while the rewrite must either project `rn` in the inner query and then re-list the wanted columns outside, or accept `rn` in the final result. Listing the columns explicitly in the outer `SELECT`, as above, keeps the two forms output-identical. ## How to handle it in an interview If you are asked for top-N-per-group, write the portable two-level version first — it is correct everywhere. Mentioning `QUALIFY` afterwards as a shortcut available in some analytical engines shows range without risking a query the interviewer's engine would reject. If the interview is explicitly for a warehouse that supports it, using it is fine, but still be able to state the standard rewrite on request, because that is the version that proves you understand *why* the extra level exists at all.