The used-car buying guide
A buyer reading a guide wants a short list of cars to look at. This capstone asks for that list, and nothing in it tells you how to build the query.
How it works
fact_vehicle_summary has one row per vehicle, with its lifetime test counts, defect totals and timeline already worked out. Filter to the vehicles that qualify, rank them inside each make and keep the first few.
QUALIFYfilters on a window function after the other clauses have run, so ranking and filtering fit in one query.- A rank needs a last tie-break column to give the same order on every run.
ROW_NUMBERgives every row its own number, whileRANKcan give several rows the same one. = FALSEkeeps the rows where a flag is false. ANULLflag is neither true nor false, so it is left out.- BigQuery bills for the columns a query reads. A table that is already summarized costs far fewer bytes than the tests it comes from.
Common mistake
Rebuilding the summary from fact_mot_test gives the same kind of answer for many times the bytes, and this check counts bytes.
Worked example
SELECT make, vehicle_id, test_counts.total_tests
FROM `learn.mot.fact_vehicle_summary`
WHERE make = 'MINI'
QUALIFY ROW_NUMBER() OVER (PARTITION BY make ORDER BY test_counts.total_tests DESC, vehicle_id ASC) <= 2The two MINI vehicles with the most tests come back, and the ranking restarts for each make.
Only the columns the query names are read from the table.