Recursive Common Table Expression

ancestors.sql

with
    people (id, name, father_id, mother_id) as (
        select 1, 'Abraham Simpson', null, null from dual
        union
        select 2, 'Mona Olsen', null, null from dual
        union
        select 3, 'Clancy Bouvier', null, null from dual
        union
        select 4, 'Jackie Gurney', null, null from dual
        union
        select 5, 'Herb Simpson', 1, 2 from dual
        union
        select 6, 'Homer Simpson', 1, 2 from dual
        union
        select 7, 'Marge Bouvier', 3, 4 from dual
        union
        select 8, 'Patty Bouvier', 3, 4 from dual
        union
        select 9, 'Selma Bouvier', 3, 4 from dual
        union
        select 10, 'Bart Simpson', 6, 7 from dual
        union
        select 11, 'Lisa Simpson', 6, 7 from dual
        union
        select 12, 'Maggie Simpson', 6, 7 from dual
        union
        select 13, 'Ling Bouvier', null, 9 from dual
    ),
    ancestors (id, name, father_id, mother_id) as (
        select id, name, father_id, mother_id from people where id = 12
        union all
        select people.id, people.name, people.father_id, people.mother_id from people join ancestors on people.id in (ancestors.father_id, ancestors.mother_id)
    )
select id, name from ancestors order by id desc;

full-paths.sql

with
    folders (id, name, parent_id) as (
        select 1, 'bin', null from dual
        union
        select 2, 'etc', null from dual
        union
        select 3, 'home', null from dual
        union
        select 4, 'opt', null from dual
        union
        select 5, 'tmp', null from dual
        union
        select 6, 'usr', null from dual
        union
        select 7, 'var', null from dual
        union
        select 8, 'bin', 6 from dual
        union
        select 9, 'lib', 6 from dual
        union
        select 10, 'log', 7 from dual
        union
        select 11, 'john', 3 from dual
        union
        select 12, 'desktop', 11 from dual
        union
        select 13, 'documents', 11 from dual
        union
        select 14, 'music', 11 from dual
    ),
    full_paths (id, full_path) as (
        select id, name from folders where parent_id is null
        union all
        select folders.id, full_paths.full_path || '/' || folders.name from folders join full_paths on folders.parent_id = full_paths.id
    )
select * from full_paths order by id;

split-by-month.sql

with
    base (id, start_date, end_date) as (
        select 1, date '2022-01-15', date '2022-03-15' from dual
        union
        select 2, date '2022-09-15', date '2022-12-31' from dual
        union
        select 3, date '2023-09-15', date '2023-09-25' from dual
    ),
    split (id, start_date, end_date) as (
        select base.id, base.start_date, least(last_day(base.start_date), base.end_date) from base
        union all
        select base.id, split.end_date + 1, least(last_day(split.end_date + 1), base.end_date) from base join split on base.id = split.id and split.end_date < base.end_date
    )
select * from split order by id, start_date, end_date;