CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformcommon-table-expressionhierarchical-datarecursive-query
David
David

Reputation: 47

Recursion in Snowflake

Long time listener, first time caller.

Im trying to learn recursion in Snowflake SQL with a classic Organizational Chart. The output Im trying for would be the total salary for each manager of employees, including both direct and indirect employees of that manager.

create table Employee(
    employeeid int primary key,
    managerid int,
    title string
);



INSERT INTO EMPLOYEE VALUES(1,NULL,'CEO');
INSERT INTO EMPLOYEE VALUES(2,1,'SVP 1');
INSERT INTO EMPLOYEE VALUES(3,1,'SVP 2');
INSERT INTO EMPLOYEE VALUES(4,2,'DIR 1');
INSERT INTO EMPLOYEE VALUES(5,2,'DIR 2');
INSERT INTO EMPLOYEE VALUES(6,2,'DIR 3');
INSERT INTO EMPLOYEE VALUES(7,3,'MGR 1');
INSERT INTO EMPLOYEE VALUES(8,3,'MGR 2');
INSERT INTO EMPLOYEE VALUES(9,3,'MGR 3');
INSERT INTO EMPLOYEE VALUES(10,9,'ASST');



create TABLE EMPLOYEE_SALARY(
    EMPLOYEE_ID INT PRIMARY KEY,
    SALARY INT
);


INSERT INTO EMPLOYEE_SALARY VALUES(1,100);
INSERT INTO EMPLOYEE_SALARY VALUES(2,90);
INSERT INTO EMPLOYEE_SALARY VALUES(3,90);
INSERT INTO EMPLOYEE_SALARY VALUES(4,80);
INSERT INTO EMPLOYEE_SALARY VALUES(5,80);
INSERT INTO EMPLOYEE_SALARY VALUES(6,80);
INSERT INTO EMPLOYEE_SALARY VALUES(7,70);
INSERT INTO EMPLOYEE_SALARY VALUES(8,70);
INSERT INTO EMPLOYEE_SALARY VALUES(9,90);
INSERT INTO EMPLOYEE_SALARY VALUES(10,60);

select  
        managerid,
        sum(salary) as salary
from        
        (        
        select         emp.managerid,
                       emp.employeeid, 
                       sal.salary
        from           employee emp
        inner join     employee_salary sal on emp.employeeid = sal.employee_id   
        )
group by 1
start with     managerid is Null
connect by     managerid = prior employeeid

The error Im getting is "SQL compilation error: error line 3 at position 12 invalid identifier 'SALARY'".

Thanks in advance for any direction you can offer.

Upvotes: 3

Views: 2804

Answers (2)

Adrian White
Adrian White

Reputation: 1804

enter image description here

I'd recommend the CTE approach from GMB however if you want your SQL to work here's one approach :

select
    managerid,
    sum(salary) salary_summed
from
(
    select
        *
    from
        (
            select
                emp.managerid,
                emp.employeeid,
                sal.salary
            from
                employee emp
                inner join employee_salary sal on emp.employeeid = sal.employee_id
        ) start with managerid is Null connect by managerid = prior employeeid
)
group by managerid

Upvotes: 0

GMB
GMB

Reputation: 222482

I would approach this with a recursive common table expression. Snowflake supports that standard syntax, and I find it easier to follow that the connect by clause:

with cte as (
    select managerid, employeeid from employee
    union all
    select c.managerid, e.employeeid
    from cte c
    inner join employee e on e.managerid = c.employeeid
)
select c.managerid, sum(s.salary) total_salaries
from cte c
inner join employee_salary s on s.employeeid = c.employeeid
group by c.managerid

Upvotes: 3

Related Questions