Recursive CTE With Examples

Are you curious about how SQL experts simplify complex queries? In this video, we dive into the power of recursive Common Table Expressions (CTEs) or so called recursive SQLs. Whether you’re preparing for a technical interview or working on a real-life data project, mastering recursive CTEs can significantly enhance your SQL writing, making it more efficient and readable.

We’ll explore how to use recursive CTEs start with simple example to scenario based examples and explain its construct in step-by-step manner, using Snowflake cloud data warehouse platform.

This tutorial is beginner-friendly and filled with practical examples you can apply in your daily SQL tasks.

YouTube Video Link

SQL Script Used In Video Tutorial

Generate Sequence Number — Manual Union Approach

-- hard-coded approach” or a “manual union approach.”
-- It involves explicitly specifying each value in the SQL query, which can be efficient for small, fixed sets of data.
select 1 as n
union all 
select 2 as n
union all 
select 3 as n
union all 
select 4 as n
union all 
select 5 as n;

-- “array unpivoting approach” or “array flattening approach.” Here’s a brief explanation of the terms:
-- but still hard coded approach.
select value::number as n from 
table(flatten(input=> split('1,2,3,4,5,6,7,8,9,10',',')))

Generate Sequence Number —Recursive Way

-- Produce set of number without using a function     "Constraints - Dont Use Any built-in database function"
-- It must begin with n (n is a positve number)       "Anchor Part"
-- it should incremented by 1                         "transformation during recursion"
-- it should produce m values                         "check point or termination check"

with recursive my_recursive_cte as 
(
    -- the first part or so called anchor part
    select 1 as n

    union all 
    
    -- the recursive part that takes the input from above query
    -- and also apply the necessary transformation or any logic
    -- and also apply the termination point

    select n+1 as nfrom my_recursive_cte where  n < 10
)
-- final cte output query
select n from my_recursive_cte;

Another Recursive SQL Example

with recursive my_recursive_cte as 
(
    -- the first part or so called anchor part
    select 0 as positive, 0 as negative, 1 as multiply

    union all 

    -- the recursive part that takes the input from above query
    select positive+1 as positive, negative-1 as negative, multiply*2 as multiply from my_recursive_cte where multiply < 50
)
-- final cte output query
select positive,negative,multiply  from my_recursive_cte;

with recursive my_date_dim_cte as 
(
    -- anchor clause
    select 
        current_date() as today,
        year(today) as year,
        quarter(today) as quarter,
        month(today) as month,
        week(today) as week,
        dayofyear(today) as day_of_year,
        dayofweek(today) as day_of_week,
        day(today) as day_of_the_month,
        dayname(today) as day_name

    union all

     -- recursive clause
    select 
        dateadd('day', -1, today) as today_r,
        year(today_r) as year,
        quarter(today_r) as quarter,
        month(today_r) as month,
        week(today_r) as week,
        dayofyear(today_r) as day_of_year,
        dayofweek(today_r) as day_of_week,
        day(today_r) as day_of_the_month,
        dayname(today_r) as day_name
    from 
        my_date_dim_cte
    where 
        today_r > '2024-08-31' 
)
select * from my_date_dim_cte

create or replace sequence date_dim_seq start with 1 increment by 1 order;
with recursive my_date_dim_cte as 
(
    -- anchor clause
    select 
        date_dim_seq.nextval as date_pk,
        to_date('2024-10-01') as today,
        year(today) as year,
        quarter(today) as quarter,
        month(today) as month,
        week(today) as week,
        dayofyear(today) as day_of_year,
        dayofweek(today) as day_of_week,
        day(today) as day_of_the_month,
        dayname(today) as day_name

    union all

     -- recursive clause
    select 
        date_dim_seq.nextval as date_pk,
        dateadd('day', 1, today) as today_r,
        year(today_r) as year,
        quarter(today_r) as quarter,
        month(today_r) as month,
        week(today_r) as week,
        dayofyear(today_r) as day_of_year,
        dayofweek(today_r) as day_of_week,
        day(today_r) as day_of_the_month,
        dayname(today_r) as day_name
    from 
        my_date_dim_cte
    where 
        today_r <= current_date()
)
select * from my_date_dim_cte;

with my_order_recursive_cte as 
(
    -- starting point
    select 
        '10001' as order_number , 
        1 as counter  

    union all

    -- recursive function
    select 
        concat('1000',counter+1) as order_number, 
        counter+1 as counter
    from my_order_recursive_cte
        where counter < 1000
),
    combine_cte as (
    select 
        a.*,
        b.order_number as gap_order
    from 
        my_order_recursive_cte a left join order_details b 
        on a.order_number = b.order_number
)
select 
    * 
from  
    combine_cte
where gap_order is null

use role sysadmin;
use database demo_db;
use schema public;

CREATE or replace TABLE employee (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id INT NULL -- Manager ID references another employee in the same table
);

INSERT INTO employee (employee_id, employee_name, manager_id) VALUES
(1, 'Alice', NULL),      -- Level 1: Alice is the CEO (no manager)
(2, 'Bob', 1),           -- Level 2: Bob reports to Alice
(3, 'Charlie', 1),       -- Level 2: Charlie reports to Alice
(4, 'Diana', 2),         -- Level 3: Diana reports to Bob
(5, 'Edward', 2),        -- Level 3: Edward reports to Bob
(6, 'Frank', 3),         -- Level 3: Frank reports to Charlie
(7, 'Grace', 3),         -- Level 3: Grace reports to Charlie
(8, 'Henry', 4),         -- Level 4: Henry reports to Diana
(9, 'Jack', 5),          -- Level 4: Jack reports to Edward
(10, 'Kevin', 6),        -- Level 4: Kevin reports to Frank
(11, 'Linda', 7),        -- Level 4: Linda reports to Grace
(12, 'Isabella', 8);     -- Level 5: Isabella reports to Henry

with recursive emp_hierarchy as (

    -- anchor query where manager is null
    select 
        employee_id,
        employee_name,
        manager_id,
        employee_name as manager_chain
    from 
        employee
    where 
        manager_id is null

    union all

    -- iterarive/recursive query where manager is not null
     select 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        concat(eh.manager_chain,'->',e.employee_name) as manager_chain
    from 
        employee e inner join emp_hierarchy eh on e.manager_id = eh.employee_id   
)
select 
    *
from emp_hierarchy;

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor query: Get top-level employees (no manager)
    SELECT 
        employee_id, 
        employee_name, 
        manager_id,
        employee_name AS manager_chain,
        1 AS depth  -- Depth starts at 1 for top-level employees
    FROM employee
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive query: Get reportees and calculate depth
    SELECT 
        e.employee_id, 
        e.employee_name, 
        e.manager_id,
        CONCAT(eh.manager_chain, ' -> ', e.employee_name) AS manager_chain,
        eh.depth + 1 AS depth  -- Increment depth by 1 for each recursion level
    FROM employee e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- Final selection: Show employee hierarchy with depth
SELECT 
    employee_id, 
    employee_name, 
    manager_id, 
    manager_chain, 
    depth
FROM employee_hierarchy
ORDER BY manager_chain;

use role sysadmin;
use database demo_db;
use schema public;

CREATE or replace TABLE car_component (
    component_id INT PRIMARY KEY,
    component_name VARCHAR(100),
    parent_component_id INT NULL -- Parent component references another component
);

INSERT INTO car_component (component_id, component_name, parent_component_id) VALUES
-- Top-level component (Car)
(1, 'Car', NULL),

-- Level 2: Main Components of the Car
(2, 'Engine', 1),
(3, 'Transmission', 1),
(4, 'Wheels', 1),

-- Level 3: Components of Engine
(5, 'Piston', 2),
(6, 'Crankshaft', 2),
(7, 'Spark Plug', 2),

-- Level 3: Components of Transmission
(8, 'Gearbox', 3),
(9, 'Clutch', 3),

-- Level 4: Components of Gearbox
(10, 'Gears', 8),
(11, 'Gear Shaft', 8),

-- Level 3: Components of Wheels
(12, 'Tire', 4),
(13, 'Rim', 4);

with recursive car_component_hierarchy as 
(
    -- start with row that has no parent as anchor clause
    select
        component_id,
        component_name,
        parent_component_id
        component_name as component_path,
        1 as depth
    from 
        car_component
    where 
        parent_component_id is null
        
    union all

    -- iterative where parent id of current row matches with anchor clause coponent id
    select
        c.component_id,
        c.component_name,
        c.parent_component_id
        concat(cch.component_name,' => ' c.component_name) as component_path,
        (depth+1) as depth
    from 
        car_component c inner join car_component_hierarchy cch on c.parent_component_id = cch.component_id  
)
SELECT 
    component_id, 
    component_name, 
    parent_component_id, 
    component_path, 
    depth
FROM car_component_hierarchy
ORDER BY component_path;