Using temporary tables via WITH (named subqueries) in Google BigQuery

use-temporary-tables-with-named-subquery.sql

-- Using temporary tables via WITH (named subqueries) in Google BigQuery; 2020-05-29
-- @see http://www.pascallandau.com/bigquery-snippets/use-temporary-tables-with-named-subquery/
WITH data as (
  SELECT 
    1 as id,
    DATE("2018-04-08") AS date,
  UNION ALL SELECT 2, DATE("2018-04-09") 
  UNION ALL SELECT 3, DATE("2018-04-10") 
  UNION ALL SELECT 4, DATE("2018-04-11") 
),
filtered_data as (
  SELECT 
    id
  FROM
    data
  WHERE
    id BETWEEN 2 and 3
)

SELECT
  *
FROM
  filtered_data

Source: Using temporary tables via WITH (named subqueries) in Google BigQuery