Date spines

spine.redshift.sql

WITH
  -- create initial data
  seq0 AS (
    SELECT 1
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
    UNION ALL
    SELECT 4
    UNION ALL
    SELECT 5
    UNION ALL
    SELECT 6
    UNION ALL
    SELECT 7
    UNION ALL
    SELECT 8
    UNION ALL
    SELECT 9
    UNION ALL
    SELECT 10
  ),
  
  -- amplify!
  seq1 AS (SELECT ROW_NUMBER() OVER () AS n FROM seq0 s1, seq0 s2),  -- 100
  seq2 AS (SELECT ROW_NUMBER() OVER () AS n FROM seq1 s1, seq1 s2),  -- 10000
  seq3 AS (SELECT ROW_NUMBER() OVER () AS n FROM seq2 s1, seq2 s2),  -- 100000000 = 100M
  seq4 AS (SELECT ROW_NUMBER() OVER () AS n FROM seq3 s1, seq3 s2),  -- 10000000000000000 = 10000T
  -- to your heart's content...

SELECT
  DATE_ADD('day', n, '1900-01-01') AS date
FROM seqN
WHERE 1=1
  AND date BETWEEN <start> AND <end>  -- optionally, constrain to some useful range
ORDER BY date DESC
;

spine.snowflake.sql

WITH
  seq0 AS (SELECT v FROM VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) AS t (v)),
  seq1 AS (SELECT t1.v * POWER(10, POWER(2, 0)) + t2.v AS v FROM seq0 t1, seq0 t2),
  seq2 AS (SELECT t1.v * POWER(10, POWER(2, 1)) + t2.v AS v FROM seq1 t1, seq1 t2),
  seq3 AS (SELECT t1.v * POWER(10, POWER(2, 2)) + t2.v AS v FROM seq2 t1, seq2 t2)

SELECT COUNT(DISTINCT v) FROM seq3
;