EXTRACT, DATE_TRUNC and DATE_DIFF
Test dates show how the tests spread over the year and when a certificate runs out. Date functions turn raw days into months and ages you can count.
How it works
DATE_TRUNC rounds each date down to the first day of its month, quarter or year, so every date in one period becomes a single value to group on. DATE_DIFF compares two dates and counts how many boundaries of the unit lie between them.
DATE_TRUNC(completed_date, MONTH)returns the first day of that month as aDATE. Other grains includeWEEK,QUARTERandYEAR.DATE_DIFF(later_date, earlier_date, DAY)counts the days from the second date to the first. Put the later date first, or the answer is negative.EXTRACT(YEAR FROM completed_date)returns one part of a date as a number.AVGignoresNULLvalues. If a column is mostlyNULL, the average describes only the few rows that have a value.
Common mistake
expiry_date belongs to the certificate that a test issues. An average over every test mixes rows of different kinds, so filter to the tests you mean before you average.
Worked example
SELECT DATE_TRUNC(completed_date, QUARTER) AS test_quarter, COUNT(*) AS test_count
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY test_quarter
ORDER BY test_quarterEvery date is rounded down to the first day of its quarter, so each quarter becomes one group.
fact_mot_test is split by month, and the filter on completed_date keeps the read to the twelve months of 2024.