Generate a sequence of numbers

-- Postgres / DuckDB.
SELECT * FROM generate_series(1, 10);                  -- 1, 2, 3, ..., 10
SELECT * FROM generate_series(1, 100, 5);              -- 1, 6, 11, ... (step = 5)

-- BigQuery.
SELECT * FROM UNNEST(GENERATE_ARRAY(1, 10)) AS n;
SELECT * FROM UNNEST(GENERATE_ARRAY(1, 100, 5)) AS n;

-- Snowflake.
SELECT seq4() AS n FROM TABLE(GENERATOR(ROWCOUNT => 10));

-- Recursive CTE — works on every engine that supports recursive CTEs.
WITH RECURSIVE numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

-- MySQL 8+ recursive form.
WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

-- Pad a chart with zero-rows for missing buckets.
WITH spine AS (SELECT n AS bucket FROM generate_series(0, 9) AS g(n)),
bucketed_data(bucket, value) AS (
  VALUES (1, 14), (3, 8), (7, 22)
)
SELECT
  s.bucket,
  COALESCE(actual.value, 0) AS value
FROM   spine s
LEFT JOIN (SELECT bucket, value FROM bucketed_data) actual USING (bucket);

-- Generate test data: 1000 fake rows.
SELECT
  n                                          AS id,
  'user_' || n                               AS username,
  RANDOM() * 1000                            AS spend
FROM   generate_series(1, 1000) AS t(n);

-- Tally table for "appears N times" expansion.
WITH templates(id, body, copies) AS (
  VALUES (1, 'reminder', 2), (2, 'invoice', 3)
)
SELECT t.*, n AS copy_num
FROM   templates t,
       generate_series(1, t.copies) AS s(n);

-- Numbers as a basis for date math (combine with INTERVAL).
SELECT DATE '2026-01-01' + (n || ' days')::INTERVAL AS day
FROM   generate_series(0, 364) AS t(n);
-- Every day in 2026.