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.

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

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

All BigQuery lessons