DECLARE, SET and IF
Many checks need a number first and a decision second. A script keeps the number between those two steps.
How it works
A script is a list of statements that run in order, each ending with a semicolon. A variable holds one value under a name: DECLARE creates it and SET fills it, often with the single number a query returns from a table such as fact_mot_test. Then IF chooses which statements run next. The last statement that runs gives the answer.
DECLARE overall_pass_rate FLOAT64;creates a variable with a name and a type. It holdsNULLuntil aSETfills it. AddDEFAULT 0.9to start it with a value instead.- A
DECLAREmust come first in its script, or first in itsBEGIN ... ENDblock, before any other statement there. Anywhere else BigQuery refuses it. SET overall_pass_rate = (SELECT ...);stores the value of an expression. A query in brackets works when it returns one column and at most one row: two rows are an error, and no rows givesNULL. The value must fit the type, so aFLOAT64variable takes 0.78 and anINT64variable refuses it.IF condition THEN ... ELSE ... END IF;runs theTHENstatements when the condition is true. Otherwise theELSEstatements run, andELSEis optional.ELSEIF condition THENadds a branch, written as one word. The conditions are checked from the top, and the first true one runs.- The answer of a script is the result of the last statement that ran. Only the branch that was taken runs, so only its
SELECTcan be the answer. - A
SETthat readsfact_mot_testfollows the usual rules for that table, including the filter oncompleted_date.
Common mistake
A variable and a column can share a name, and the column wins without any warning. After DECLARE make STRING DEFAULT 'BMW', the condition WHERE make = make compares the column with itself and keeps every test. Give a variable a name that no column of the tables that statement reads has, such as target_make.
Worked example
DECLARE target_make STRING DEFAULT 'HONDA';
DECLARE minimum_tests INT64 DEFAULT 250;
DECLARE test_count INT64;
SET test_count = (
SELECT COUNT(*)
FROM `learn.mot.fact_mot_test`
WHERE make = target_make
AND completed_date BETWEEN '2024-01-01' AND '2024-12-31'
);
IF test_count >= minimum_tests THEN
SELECT
target_make AS make,
ROUND(SAFE_DIVIDE(COUNTIF(test_result = 'PASSED'), COUNT(*)), 3) AS pass_rate
FROM `learn.mot.fact_mot_test`
WHERE make = target_make
AND completed_date BETWEEN '2024-01-01' AND '2024-12-31';
ELSE
SELECT target_make AS make, test_count AS tests, 'Too few tests for a pass rate' AS note;
END IF;The make and the minimum are set once at the top with DEFAULT, so the rule changes in one place.
The SET counts the 2024 tests of one make. It needs the same date filter as any query on fact_mot_test. Honda has 219 tests in 2024, which is below 250.
The count is below the minimum, so the SELECT under ELSE is the one that runs. It returns make, tests and note. Change target_make to FORD, which has 931 tests, and the first SELECT runs instead and returns make and pass_rate.
Because the sample never changes, the same branch runs every time, and one plain SELECT written for that branch returns the same row as the script. Even a check that only picks a label could be one CASE query here. On a table that keeps changing it could not: a single query always returns one set of columns, and these two branches return different ones, so a script is the tool that follows the data.
This track asks for the script anyway. The guidance of each challenge names the constructs it needs, such as DECLARE and IF, and the check refuses an answer that leaves one out, even when its rows are right.