WITH expressions in BigQuery

So I recently discovered the WITH expression in BigQuery SQL.

Not to be confused with the WITH clause, which we use to define common table expressions (CTEs).

👉 What does a WITH expression do?
It lets you define a series of variables, scoped to a single expression. Each variable can reference previously defined ones (and table columns), and in the end, the whole expression returns a result.

📌 Where could this be useful?
Think back to the time before QUALIFY was supported. We often had to create an extra CTE just to filter with WHERE rn = 1 or for similar windowed calculations. When QUALIFY came, it saved us a bunch of boilerplate CTEs.
Well, WITH expressions have the potential to help in the same way — but for non-window calculations.

When working with complex formulas, you can’t reference (within the same SELECT) a column you just defined. The usual workaround is to push it into another CTE — which works, but feels verbose. I still opted to do it since it's important that the code stayed readable and maintainable.
Now WITH expressions give us a cleaner option and help avoid those 7-operand expressions. I, for one, plan on trying them out ASAP.

-- WITH clause
WITH input_data AS (
  SELECT 1 AS product_id, 5 AS quantity, 100 AS base_price, 0.15 AS discount_rate, 0.21 AS tax_rate
  UNION ALL
  SELECT 2 AS product_id, 3 AS quantity, 80 AS base_price, 0.12 AS discount_rate, 0.21 AS tax_rate
  UNION ALL
  SELECT 3 AS product_id, 2 AS quantity, 75 AS base_price, 0.10 AS discount_rate, 0.21 AS tax_rate
)



--WITH expression
SELECT

product_id,

WITH(
  discounted_price AS base_price * (1 - discount_rate), -- variable 1
  price_incl_tax AS discounted_price * (1 + tax_rate), -- variable 2
  quantity * price_incl_tax) AS sales_amount           -- result


FROM input_data

Has anyone here used them already? Any thoughts? Docs here.