Query a in-memory, simulated table in BigQuery

query_simulated_table.sql

-- Simulate a table containing the total sales for the last 7 days
WITH sales_total AS (
    SELECT 1000 AS total_sales, DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AS day UNION ALL
    SELECT  850 AS total_sales, DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY) AS day UNION ALL
    SELECT 1200 AS total_sales, DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AS day UNION ALL
    SELECT  700 AS total_sales, DATE_SUB(CURRENT_DATE(), INTERVAL 4 DAY) AS day UNION ALL
    SELECT 1050 AS total_sales, DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY) AS day UNION ALL
    SELECT  900 AS total_sales, DATE_SUB(CURRENT_DATE(), INTERVAL 6 DAY) AS day UNION ALL
    SELECT  800 AS total_sales, DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AS day
)

-- Query the simulated table
SELECT * FROM sales_total WHERE day = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) -- get the sales from the day before