-
Notifications
You must be signed in to change notification settings - Fork 0
/
202252_solution.sql
55 lines (52 loc) · 1.74 KB
/
202252_solution.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
with add_level as (
select
*,
array_pop_back(array_pop_front(string_split(team_hierarchy, '|'))) as team_hierarchy_list,
array_pop_back(array_pop_front(string_split(dependent_team_ids, '|')))[1] as dependent_team_id,
array_length(array_pop_back(array_pop_front(string_split(employee_id_hierarchy, '|')))) as hierarchy_level
from {{ ref("src_week_202252_employees") }}
),
subdepts as (
select distinct
case
dense_rank() over(
partition by
department
order by
hierarchy_level
)
when 2 then dependent_team_id
else null
end as subdept_team_id
from add_level
where department != 'Executives'
and title not like '%Administrator%'
),
population as (
select
add_level.*,
coalesce(subdepts_team.subdept_team_id, subdepts_hierarchy.subdept_team_id) as subdept_team_id
from add_level
left join subdepts subdepts_team
on add_level.dependent_team_id = subdepts_team.subdept_team_id
left join subdepts subdepts_hierarchy
on list_contains(add_level.team_hierarchy_list, subdepts_hierarchy.subdept_team_id)
)
select
population.position_id,
population.employee_id,
population.title,
population.department,
population.supervisor_id,
population.team_id,
population.direct_reports,
population.team_name,
population.team_hierarchy,
population.employee_id_hierarchy,
population.dependent_team_ids,
population.hierarchy_level,
population.subdept_team_id,
teams.team_name as subdept_name
from population
left join {{ ref("src_week_202252_teams") }} teams
on population.subdept_team_id = teams.team_id