Recursion in Snowflake

Question

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.

GMB GMB · Accepted Answer · 2020-07-24T23:45:59

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

Recursion in Snowflake

1 Answers