A CTE referenced more than once is evaluated more than once

What it is

A common table expression is a name for a query, not a temporary table. Each reference is expanded and executed independently unless the optimizer happens to reuse the result — which is not guaranteed and not something to rely on.

Why it costs money

• The mental model most people bring is "compute once, use many". BigQuery does not promise that.
• It compounds: a CTE reading a large table, referenced four times, scans that table four times.
• Query length hides it. Nobody counts CTE references in a 300-line query.

Worked example

WITH base AS (
  SELECT * FROM `analytics.events` WHERE event_date = CURRENT_DATE()
)
SELECT (SELECT COUNT(*) FROM base) AS total,
       (SELECT COUNT(DISTINCT user_id) FROM base) AS users,
       (SELECT SUM(revenue) FROM base) AS revenue

-- One pass:
SELECT COUNT(*) AS total,
       COUNT(DISTINCT user_id) AS users,
       SUM(revenue) AS revenue
FROM `analytics.events`
WHERE event_date = CURRENT_DATE()

How to fix it

1. 1Aggregate in a single pass where the query allows it.
2. 2If the intermediate result is genuinely needed several times, write it to a temporary table and query that.
3. 3For a repeated, expensive intermediate, a materialized view pays for itself quickly.