Three-table join
Real questions often need three tables, such as tests, their defects and the vehicle behind them. Each join can change how many rows there are, so check the counts.
How it works
A join can multiply rows. A test with three defects becomes three rows after it joins to fact_defect, and every column of that test repeats on each of them. COUNT(DISTINCT mot_test_id) counts each test once again.
- Chain the joins with
FROM a JOIN b ON ... LEFT JOIN c ON .... EachONnames the two tables it connects. - Use a
LEFT JOINtofact_defectwhen tests without any defect must still be counted. COUNT(*)counts joined rows andCOUNT(DISTINCT m.mot_test_id)counts tests. After a fan-out the two differ.SAFE_DIVIDE(a, b)returnsNULLinstead of an error when b is 0.fact_defectis also partitioned by month. Put itscompleted_daterange in theONclause of theLEFT JOIN, where it is allowed becausefact_defectis on the right.
Common mistake
Counting joined rows instead of distinct tests. A test with three dangerous defects then counts three times, and the rate looks worse than it is.
Worked example
SELECT COUNT(*) AS joined_rows, COUNT(DISTINCT m.mot_test_id) AS tests
FROM `learn.mot.fact_mot_test` AS m
LEFT JOIN `learn.mot.fact_defect` AS fd
ON m.mot_test_id = fd.mot_test_id
AND fd.completed_date = '2024-03-12'
WHERE m.completed_date = '2024-03-12'Every test stays, and each defect adds one more row for its test.
joined_rows is at least tests, and it is larger when some tests have several defects.