Top n per group with QUALIFY
The worst three vehicles per make is one of the most common report shapes. QUALIFY gives it in one query, with no wrapping subquery.
How it works
WHERE filters rows before any window function runs. QUALIFY filters after, so it can test a window result such as a row number. You number the vehicles inside each make, then keep the ones numbered n or lower.
- Write
QUALIFY ROW_NUMBER() OVER (PARTITION BY make ORDER BY ...) <= 3afterWHERE. The window function does not need to appear inSELECT. - A window function is not allowed in
WHERE. BigQuery refuses the query, which is the sign to move the condition toQUALIFY. ROW_NUMBERwith a unique tie-breaker such asvehicle_idreturns exactly n rows per group.RANKcan return more than n when rows tie.WHEREruns first, so a filter such as a minimum number of tests removes vehicles before the numbering starts.QUALIFYdoes not sort the output. Add anORDER BYat the end when the row order matters.
Common mistake
A filter that removes vehicles belongs in WHERE and the top-n condition belongs in QUALIFY. Moving a minimum-test filter into QUALIFY, or ranking first and filtering after, changes which vehicles reach the top n.
Worked example
SELECT make, vehicle_id, defect_stats.major_defects
FROM `learn.mot.fact_vehicle_summary`
WHERE make IN ('KIA', 'SUZUKI')
QUALIFY ROW_NUMBER() OVER (PARTITION BY make ORDER BY defect_stats.major_defects DESC, vehicle_id) = 1The window numbers the KIA and SUZUKI vehicles by major defects inside each make. QUALIFY keeps number 1 only, so one row per make comes back.
The vehicle with the most major defects in each make is the one you see.