MAX_BY, LOGICAL_AND and STRING_AGG
COUNTIF counts the rows that meet a condition. Other questions about a group need a different aggregate, such as the result of its latest test or a list of its values.
How it works
Each group is a set of rows. MAX_BY(x, y) finds the row with the largest y and returns its x. LOGICAL_AND and LOGICAL_OR reduce the true and false values of the group to one value, and STRING_AGG writes its values as one text.
MAX_BY(x, y)returns the value ofxfrom the row whereyis largest.MIN_BY(x, y)does the same for the smallesty. They replace a self-join on the maximum andARRAY_AGG(x ORDER BY y DESC LIMIT 1)[OFFSET(0)].yis compared the wayMAXcompares it. If two rows tie for the largesty, you get thexof one of them, and nothing says which.ANY_VALUE(x)returns thexof one row of the group, and nothing says which. That is fine for a column that is the same on every row of the group, such as the fuel type of a vehicle when you group by vehicle. On a column that differs inside the group, you get one of its values.LOGICAL_AND(condition)is true when the condition is true on every row of the group.LOGICAL_OR(condition)is true when it is true on at least one row.- Both skip the rows where the condition is
NULL, the waySUMskips aNULLvalue.LOGICAL_AND(odometer.value < 100000)ignores a test with no reading. Both giveNULLwhen no row has a value. STRING_AGG(x, separator)joins the non-NULLvalues ofxinto one text. The separator is a comma when you leave it out.STRING_AGG(DISTINCT x)joins each value once.ORDER BYinside the call sorts the values before they are joined, andLIMIT nkeeps the first n values, as inSTRING_AGG(model, ', ' ORDER BY model LIMIT 3). Without anORDER BYthe order of the values is not defined and can change between runs.
Common mistake
A result can look right and still be one of several possible answers. ANY_VALUE on a column that differs inside the group, a MAX_BY with tied keys and a STRING_AGG with no ORDER BY can each give another answer on the next run.
Worked example
SELECT vehicle_snapshot.fuel_type AS fuel_type,
MAX(odometer.value_normalized_km) AS highest_km,
MAX_BY(vehicle_snapshot.model, odometer.value_normalized_km) AS model_at_highest_km,
STRING_AGG(DISTINCT make, ', ' ORDER BY make LIMIT 3) AS first_makes
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2024-10-01' AND '2024-10-31'
GROUP BY vehicle_snapshot.fuel_type
ORDER BY fuel_typeEach fuel type of the October 2024 tests gets its highest reading, the model of the test that had it, and up to three makes in alphabetical order.
MAX and MAX_BY look at the same column, so model_at_highest_km is the model that goes with highest_km.