df-drills pandas · Polars · DuckDB SQL · DuckDB Relational 题目列表 登录

组织层级深度

困难 21-classic 面试经典题型 hierarchyrecursiveself-joinloop

求每个员工在汇报链上的层级深度:没有经理的人(manager_id 为 null)深度为 0,他的直接下属深度为 1,下属的下属为 2,依此类推。

  • 公司里可能有不止一个没有经理的人(多棵汇报树),每棵树的根深度都是 0;只有自己一个人的树也算。
  • 汇报链没有环,所有非 null 的 manager_id 都能在表里找到;汇报链可能很深,层数不固定。
  • dept_id 等其他列与本题无关(可能为 null)。

输入

  • employees(emp_id, name, dept_id, manager_id, hire_date, salary, title, email)

输出

列为 emp_id, name, depth(整数),每个员工恰好一行,行顺序不限。

输入表 (case base,共 2 个 case:base、forest)

employees hr/employees
#emp_idint64namestringdept_idint64manager_idint64hire_datedatesalaryint64titlestringemailstring
1 1 Grace Liu 1 null 2015-10-01 98000 CEO grace.liu@corp.example
2 2 Leo Wang 2 1 2017-09-09 72000 VP Engineering leo.wang@corp.example
3 3 Sara Chen 3 1 2020-06-05 70000 VP Sales sara.chen@corp.example
4 4 Lucas Zhang 4 2 2023-04-20 52000 Engineering Manager lucas.zhang@corp.example
5 5 Mia Zhao 5 2 2016-07-16 52000 Data Manager mia.zhao@corp.example
6 6 Kevin Wu 3 3 2016-10-23 45000 Sales Manager kevin.wu@corp.example
7 7 Lily Xu 6 4 2025-03-17 48000 Infra Lead lily.xu@corp.example
8 8 Tom Huang 4 4 2022-01-03 46000 Senior Engineer tom.huang@corp.example
9 9 Liam Zhou 4 4 2020-10-01 39000 Software Engineer liam.zhou@corp.example
10 10 Nina Yang 4 4 2020-10-02 39000 Software Engineer nina.yang@corp.example
11 11 Oscar Lin 4 8 2022-07-15 26000 Junior Engineer oscar.lin@corp.example
12 12 Linda Ma 5 5 2016-08-25 41000 Data Engineer linda.ma@corp.example
13 13 Eric Sun 5 5 2021-09-09 33000 Data Analyst eric.sun@corp.example
14 14 Ivy He 5 5 2018-05-29 41000 Data Engineer ivy.he@corp.example
15 15 Lei Gao 5 12 2016-06-02 27000 Junior Data Engineer lei.gao@corp.example
16 16 Hao Guo 6 7 2016-12-18 44000 Site Reliability Engineer hao.guo@corp.example
17 17 Fang Luo 6 7 2020-11-11 38000 Infra engineer fang.luo@corp.example
18 18 Jun Liang 6 16 2020-09-08 25000 Junior Engineer jun.liang@corp.example
19 19 Dana Song 3 6 2016-10-13 31000 Account Executive dana.song@corp.example
20 20 Victor Tang 3 6 2018-09-12 31000 Account Executive victor.tang@corp.example
显示前 20 行,共 40 行

期望输出 (case base,行顺序不限)

#emp_idInt64nameStringdepthInt32
1 1 Grace Liu 0
2 2 Leo Wang 1
3 3 Sara Chen 1
4 24 Zoe Cao 1
5 4 Lucas Zhang 2
6 5 Mia Zhao 2
7 6 Kevin Wu 2
8 27 Rui Lu 2
9 28 Uma Jiang 2
10 33 Larry Ren 2
11 7 Lily Xu 3
12 8 Tom Huang 3
13 9 Liam Zhou 3
14 10 Nina Yang 3
15 12 Linda Ma 3
16 13 Eric Sun 3
17 14 Ivy He 3
18 19 Dana Song 3
19 20 Victor Tang 3
20 21 Wen Han 3
21 22 Lu Feng 3
22 29 Xin Cai 3
23 30 Bob Pan 3
24 32 Amy Yu 3
25 36 王磊 3
26 11 Oscar Lin 4
27 15 Lei Gao 4
28 16 Hao Guo 4
29 17 Fang Luo 4
30 23 Yan Deng 4
31 25 Ping Peng 4
32 26 Qing Xie 4
33 31 Chen Du 4
34 34 Wei Shi 4
35 35 Zhang Min 4
36 37 李静 4
37 39 Logan Bai 4
38 40 Sophie Fan 4
39 18 Jun Liang 5
40 38 Henry Kong 5
共 40 行