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.

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) = 1

The 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.

All BigQuery lessons