Expressions, aliases and CASE
Analysts rarely want a raw column. They want a label, a calculated value, or a band: a name for a range of numbers, such as High mileage for every reading over 150000.
How it works
An expression computes a new value for every row that reaches SELECT, and AS gives that value a name. CASE picks one of several values by checking its conditions from top to bottom and stopping at the first one that is true.
- Name every computed column with
AS, as inSELECT ... AS mileage_band. Without a name BigQuery gives the column a name of its own. CASE WHEN condition THEN value ... ELSE value ENDreturns the value of the firstWHENthat is true.- When no
WHENmatches and there is noELSE,CASEreturnsNULL. - A comparison with
NULLis never true, so aNULLreading matches noWHENat all. TestIS NULLfirst whenNULLneeds its own label.
Common mistake
A NULL odometer reading fails odometer.value_normalized_km > 150000, so ELSE labels it like an ordinary reading. Put an IS NULL branch before the comparisons when missing values need a label of their own.
Worked example
SELECT vehicle_id,
CASE WHEN test_result = 'PASSED' THEN 'ready to sell'
WHEN test_result = 'FAILED' THEN 'needs work'
END AS verdict
FROM `learn.mot.fact_mot_test`
WHERE make = 'HONDA'
AND completed_date BETWEEN '2024-03-01' AND '2024-03-31'Each test gets a verdict from its result. The first WHEN that is true decides the value.
Every row has one of the two results, so no verdict is NULL here.