INNER JOIN
Most useful questions need facts from two tables at once. In the MOT tables the shared id has two names: vehicle_id in fact_mot_test and registration in dim_vehicle.
How it works
Take each row of the left table and find the rows of the right table that satisfy the ON condition. An INNER JOIN keeps a row only when a match exists on both sides. The result has one row per matching pair.
- Write
FROM a JOIN b ON a.key = b.key.JOINon its own meansINNER JOIN. - Give each table a short alias and put it in front of every column, so it is clear which table a column comes from.
fact_mot_testjoins todim_vehicleonvehicle_id = registration. The two columns have different names but hold the same identifier.fact_mot_testis split by month and needs a filter oncompleted_date. One day costs the same as the whole month, because a month is the smallest piece BigQuery reads.
Common mistake
In the real warehouse, dim_vehicle keeps a closed row with is_current = FALSE for a vehicle whose details changed. Joining without is_current = TRUE then repeats a test once per version of the vehicle.
Worked example
SELECT d.spec.make AS make, COUNT(*) AS vehicles
FROM `learn.mot.dim_vehicle` AS d
JOIN `learn.mot.fact_vehicle_summary` AS s ON d.registration = s.vehicle_id
WHERE d.is_current = TRUE
GROUP BY make
ORDER BY vehicles DESC, make
LIMIT 3Each dim_vehicle row is paired with the summary row that has the same identifier. A vehicle with no summary row drops out.
The result counts vehicles per make among the paired rows.