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.

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.

All BigQuery lessons