-- Query at specific timestamp
SELECT * FROM table_name AT(TIMESTAMP => '2024-01-15 10:00:00'::timestamp);

-- Query before timestamp
SELECT * FROM table_name BEFORE(TIMESTAMP => '2024-01-15 10:00:00'::timestamp);

-- Query at offset (seconds ago)
SELECT * FROM table_name AT(OFFSET => -3600);  -- 1 hour ago

-- Query at specific statement
SELECT * FROM table_name AT(STATEMENT => 'query_id')

-- Query at specific timestamp
SELECT * FROM table_name
FOR SYSTEM_TIME AS OF '2024-01-15 10:00:00';

-- Query at timestamp expression
SELECT * FROM table_name
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)

-- Query at specific version
SELECT * FROM table_name VERSION AS OF 5;

-- Query at specific timestamp
SELECT * FROM table_name TIMESTAMP AS OF '2024-01-15 10:00:00';

-- Query at timestamp expression
SELECT * FROM table_name TIMESTAMP AS OF date_sub(current_timestamp(), 1)

-- See data as it was yesterday
SELECT 
    product_id,
    product_name,
    price,
    last_updated
FROM products
AT(TIMESTAMP => DATEADD(day, -1, CURRENT_TIMESTAMP()))
WHERE category = 'Electronics'

SELECT 
    product_id,
    product_name,
    price,
    last_updated
FROM products
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
WHERE category = 'Electronics'

SELECT 
    product_id,
    product_name,
    price,
    last_updated
FROM products
TIMESTAMP AS OF date_sub(current_timestamp(), 1)
WHERE category = 'Electronics'

SELECT 
    current.product_id,
    current.product_name,
    current.price as current_price,
    historical.price as price_yesterday,
    current.price - historical.price as price_change,
    ROUND((current.price - historical.price) * 100.0 / historical.price, 2) as pct_change
FROM products current
LEFT JOIN products AT(OFFSET => -86400) historical  -- 24 hours ago
    ON current.product_id = historical.product_id
WHERE current.price != historical.price OR historical.price IS NULL

SELECT 
    current.product_id,
    current.product_name,
    current.price as current_price,
    historical.price as price_yesterday,
    current.price - historical.price as price_change,
    ROUND((current.price - historical.price) * 100.0 / historical.price, 2) as pct_change
FROM products current
LEFT JOIN products 
    FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) historical
    ON current.product_id = historical.product_id
WHERE current.price != historical.price OR historical.price IS NULL

SELECT 
    current.product_id,
    current.product_name,
    current.price as current_price,
    historical.price as price_yesterday,
    current.price - historical.price as price_change,
    ROUND((current.price - historical.price) * 100.0 / historical.price, 2) as pct_change
FROM products current
LEFT JOIN products TIMESTAMP AS OF date_sub(current_timestamp(), 1) historical
    ON current.product_id = historical.product_id
WHERE current.price != historical.price OR historical.price IS NULL

-- Create backup table from before delete
CREATE TABLE customers_backup AS
SELECT *
FROM customers
BEFORE(STATEMENT => '01a6a3b4-0000-5678-0000-000012345678');  -- Query ID of DELETE

-- Or restore entire table
CREATE OR REPLACE TABLE customers AS
SELECT * FROM customers AT(OFFSET => -3600);  -- 1 hour ago

-- Create backup from before delete
CREATE OR REPLACE TABLE `project.dataset.customers_backup` AS
SELECT *
FROM `project.dataset.customers`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)

-- Restore from previous version
RESTORE TABLE customers TO VERSION AS OF 5;

-- Or create backup
CREATE TABLE customers_backup AS
SELECT * FROM customers VERSION AS OF 5

-- Find all changes to a specific customer in last 7 days
WITH time_points AS (
    SELECT DATEADD(hour, -seq.value, CURRENT_TIMESTAMP()) as check_time
    FROM TABLE(GENERATOR(ROWCOUNT => 168))  -- 7 days * 24 hours
    AS seq
)
SELECT DISTINCT
    tp.check_time,
    c.customer_id,
    c.email,
    c.status,
    c.tier
FROM time_points tp
CROSS JOIN customers AT(TIMESTAMP => tp.check_time) c
WHERE c.customer_id = 12345
ORDER BY

-- View table history
DESCRIBE HISTORY customers;

-- Query specific version
SELECT * FROM customers VERSION AS OF 10;

-- See all versions of a specific row
SELECT 
    version,
    customer_id,
    email,
    tier,
    _commit_timestamp
FROM (
    SELECT 0 as version, customer_id, email, tier, current_timestamp() as _commit_timestamp
    FROM customers VERSION AS OF 0
    WHERE customer_id = 12345
    
    UNION ALL
    
    SELECT 1 as version, customer_id, email, tier, current_timestamp() as _commit_timestamp
    FROM customers VERSION AS OF 1
    WHERE customer_id = 12345
    
    -- ... repeat for each version
)
ORDER BY

-- Join current orders with historical customer data at order time
SELECT 
    o.order_id,
    o.order_date,
    o.customer_id,
    -- Get customer tier as it was when order was placed
    c_historical.tier as tier_at_order_time,
    c_current.tier as current_tier,
    o.order_total
FROM orders o
JOIN customers FOR SYSTEM_TIME AS OF o.order_date c_historical  -- BigQuery syntax
    ON o.customer_id = c_historical.customer_id
JOIN customers c_current
    ON o.customer_id = c_current.customer_id
WHERE o.order_date >= '2024-01-01'