BigQuery Cheat Sheet

CREATE TABLE analytics.orders (
  id INT64,
  customer_id INT64,
  total NUMERIC,
  created_at TIMESTAMP
)
PARTITION BY DATE(created_at)
CLUSTER BY customer_id;

CREATE OR REPLACE TABLE analytics.recent_orders AS
SELECT * FROM analytics.orders WHERE created_at > CURRENT_TIMESTAMP() - INTERVAL 30 DAY;

SELECT COUNT(*)
FROM analytics.orders
WHERE DATE(created_at) BETWEEN '2026-08-01' AND '2026-08-31';

SELECT * FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260801' AND '20260831';

CREATE OR REPLACE VIEW analytics.recent_orders AS
SELECT * FROM analytics.orders WHERE created_at > CURRENT_TIMESTAMP() - INTERVAL 30 DAY;

INSERT INTO analytics.orders (id, customer_id, total)
VALUES (1, 10, 49.90), (2, 11, 12.00);

CREATE OR REPLACE TABLE analytics.orders AS
SELECT * FROM analytics.orders_staging;

MERGE analytics.orders t
USING analytics.orders_staging s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET total = s.total
WHEN NOT MATCHED THEN INSERT ROW;

CREATE OR REPLACE TABLE analytics.orders AS
SELECT * EXCEPT(rn) FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at DESC) AS rn
  FROM analytics.orders
)
WHERE rn = 1;

SELECT * FROM analytics.orders TABLESAMPLE SYSTEM (1 PERCENT);

DELETE FROM t WHERE event_date < '2024-01-01'

SELECT COUNT(DISTINCT customer_id)          AS exact,
       APPROX_COUNT_DISTINCT(customer_id)   AS approx
FROM analytics.orders;

SELECT customer_id, total,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
FROM analytics.orders;

SELECT * FROM analytics.orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) = 1;

SELECT id FROM analytics.orders_2025
UNION ALL
SELECT id FROM analytics.orders_2026;

SELECT * FROM (SELECT customer_id, status, total FROM analytics.orders)
PIVOT (SUM(total) FOR status IN ('new', 'shipped', 'cancelled'));

SELECT STDDEV_SAMP(total), STDDEV_POP(total) FROM analytics.orders;

FOR record IN (SELECT id FROM analytics.orders LIMIT 10) DO
  SELECT record.id;
END FOR;

APPROX_QUANTILES(x, 100)[OFFSET(50)]

APPROX_QUANTILES(x, 100)[OFFSET(95)]

LAG(x) OVER (ORDER BY day)

QUALIFY ROW_NUMBER() OVER (...) = 1

SELECT CURRENT_DATE(), CURRENT_DATETIME(), CURRENT_TIMESTAMP();

SELECT CAST(ts AS DATE)                          AS day_utc,
       DATE(ts, "Europe/Helsinki")               AS day_local,
       DATETIME(ts, "Europe/Helsinki")           AS local_wall_clock
FROM analytics.events;

SELECT DATE_ADD(day, INTERVAL 7 DAY),
       TIMESTAMP_SUB(created_at, INTERVAL 1 HOUR)
FROM analytics.orders;

SELECT DATE_DIFF(delivered_on, ordered_on, DAY)              AS days,
       TIMESTAMP_DIFF(delivered_at, created_at, HOUR)        AS hours
FROM analytics.orders;

SELECT DATE_TRUNC(DATE(created_at), MONTH) AS month, COUNT(*)
FROM analytics.orders GROUP BY month ORDER BY month;

SELECT FORMAT_DATE('%Y-%m-%d', day),
       FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', created_at, 'Europe/Helsinki')
FROM analytics.orders;

SELECT PARSE_DATE('%d/%m/%Y', '31/08/2026'),
       PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%SZ', raw_ts)
FROM analytics.raw_events;

DATE(ts, 'Europe/Helsinki')

EXTRACT(MONTH FROM d)

UNNEST(GENERATE_DATE_ARRAY(a, b)) AS day

SELECT CONCAT(first_name, ' ', last_name) AS name,  -- NULL if any part is NULL
       first_name || ' ' || last_name     AS same_thing
FROM analytics.customers;

SELECT UPPER(name), LOWER(email), TRIM(code), LENGTH(name)
FROM analytics.customers;

SELECT SUBSTR(sku, 1, 3) AS prefix FROM analytics.order_items;

SELECT * FROM analytics.customers WHERE CONTAINS_SUBSTR(email, '@example.com');

SELECT * FROM analytics.customers
WHERE email LIKE '%@example.com'
   OR email LIKE ANY ('%@acme.io', '%@acme.dev');

SELECT * FROM analytics.customers WHERE REGEXP_CONTAINS(email, r'^[a-z.]+@acme\.(io|dev)$');

SELECT REGEXP_EXTRACT(url, r'utm_source=([^&]+)') AS source FROM analytics.sessions;

SELECT REGEXP_REPLACE(phone, r'[^0-9]', ''), REPLACE(sku, '-', '')
FROM analytics.customers;

SELECT REPLACE(path, '/v1/', '/v2/') FROM analytics.requests;

SELECT SPLIT(tags, ',') AS tag_array FROM analytics.posts;

SELECT customer_id, STRING_AGG(sku, ', ' ORDER BY sku)
FROM analytics.order_items GROUP BY customer_id;

SELECT ['a', 'b', 'c'] AS tags, ARRAY_AGG(sku) AS skus
FROM analytics.order_items;

SELECT o.id, tag
FROM analytics.orders o, UNNEST(o.tags) AS tag;

SELECT ARRAY_LENGTH(tags) FROM analytics.orders;

SELECT ARRAY_TO_STRING(tags, ', ') FROM analytics.orders;

SELECT JSON_VALUE(payload, '$.user.id')   AS user_id,   -- scalar, unquoted
       JSON_QUERY(payload, '$.items')     AS items      -- keeps JSON
FROM analytics.events;

SELECT CAST(total AS STRING),
       SAFE_CAST(raw_amount AS NUMERIC)  -- NULL instead of an error
FROM analytics.orders;

SELECT COALESCE(nickname, first_name, 'there'),
       IFNULL(discount, 0)
FROM analytics.customers;

SELECT SAFE_DIVIDE(total, NULLIF(item_count, 0)) FROM analytics.orders;

SELECT * FROM analytics.orders WHERE day BETWEEN '2026-08-01' AND '2026-08-31';

SELECT LEAST(list_price, sale_price), GREATEST(created_at, updated_at)
FROM analytics.products;

SELECT IF(total > 100, 'large', 'small'),
       CASE WHEN total > 100 THEN 'large' ELSE 'small' END
FROM analytics.orders;

SAFE_DIVIDE(x, y)