Recursive CTEs for data generation

recursive-ctes-data-generation.sql

/*
    Recursive CTEs for data generation

    DuckDB version: 1.0.0

    Bill Wallis, 2024-10-18
*/


select version();


/* numbers 1 to 10 */
with recursive numbers(number) as (
        select 1
    union all
        select number + 1
        from numbers
        where number < 10
)

select *
from numbers
;


/* all days in the current month */
with recursive this_month(dt) as (
        select date_trunc('month', current_date)::date
    union all
        select dt + interval '1 day'
        from this_month
        where dt < date_trunc('month', current_date) + interval '1 month -1 day'
)

select *
from this_month
;


/* split a string into a row per character */
with recursive

words(word) as (values ('something'), ('something else')),

letters(word, letter, step) as (
        select
            word,
            substring(word from 1 for 1),
            2
        from words
    union all
        select
            word,
            substring(word from step for 1),
            step + 1
        from letters
        where step <= length(word)
)

select letter
from letters
order by word, step
;


------------------------------------------------------------------------
------------------------------------------------------------------------

/* numbers 1 to 10 */
select *
from generate_series(1, 10) as n(number)
;


/* split a string into a row per character */
with words(word) as (values ('something'), ('something else'))

select unnest(string_to_array(word, '')) from words
;

This is part of the following LinkedIn post:

https://www.linkedin.com/posts/bill-wallis_sqlwithbill-sql-activity-7253329087639834625-04rG