need to know about the recursive view in snowflake

Question

I'm new to snowflake. And I need to know about the recursive view in snowflake. Can someone explain it with an example. And why recursive views are used. Non-Materialized view only

I need a really good example. Thank u in advance

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);

INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);

CREATE or replace RECURSIVE VIEW employee_hierarchy_02 (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS (
      -- Start at the top of the hierarchy ...
      SELECT title, employee_ID, manager_ID, NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 'President' AS "MGR TITLE"
        FROM employees
        WHERE title = 'President'
      UNION all
      -- ... and work our way down one level at a time.
      SELECT employees.title, 
             employees.employee_ID, 
             employees.manager_ID, 
             employee_hierarchy_02.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", 
             employee_hierarchy_02.title AS "MGR TITLE"
        FROM employees INNER JOIN employee_hierarchy_02
        WHERE employee_hierarchy_02.employee_ID = employees.manager_ID
);

what the above really means

Answer 1

so here is the above example but swapped around to a CTE form:

First we have a table of data, it's just 6 rows of data, but if you look at how it white space formatted you can see a hierarchical structure, just like might happen in a work place:

with recursive employees (title, employee_ID, manager_ID) as (
    select * from values
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20)
)

next we have the actual recursive part of the CTE, this is like a WHILE loop in other programming languages, I have added extra column to track the recursion, which starts at zero, so each to that goes up, the recursion is doing another loop.

, employee_hierarchy_02 (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE", recursive_level) AS (
    -- Start at the top of the hierarchy ...
    SELECT 
        title, 
        employee_ID, 
        manager_ID, 
        NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 
        'President' AS "MGR TITLE",
        0 as recursive_level
    FROM employees
    WHERE title = 'President'
        
    UNION all
      
    -- ... and work our way down one level at a time.
    SELECT 
        e.title, 
        e.employee_ID, 
        e.manager_ID, 
        h.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", 
        h.title AS "MGR TITLE",
        h.recursive_level + 1 as recursive_level
    FROM employees as e
    INNER JOIN employee_hierarchy_02 as h
    WHERE h.employee_ID = e.manager_ID
)
select * from employee_hierarchy_02

this gives:

this shows the 0'th layer of the recursion (the first part of the UNION ALL block as the only row that title is President, which we can see in the data there is only one of.

Then the two people reporting to the president are pulled in in loop 1, then the 3 people reporting to those 2 people are pulled in loop 2. Loop 3 finds no people that report to those 3 people so the looping stops.

Now, if I understand your question is not the above "how does it work" but "why would you do this. Well you want want to know how to allocate the count of employees, or sum or salary for each manager and need to know where/how to allocate the values. or other stuff... really I have never used it, and when I think of it as a solution, I tend to avoid it if I can. But it is a tool, and it's there.