Interview Guides

Find Employee Manager Relationships | Dataford Interview Questions - Dataford - Ace your Interview

Find Employee Manager Relationships

Medium

SQL & Data Manipulation

JoinsCTEsSelf-Joins

Problem

Task

At Nimbus Analytics, employee reporting lines are stored in a single table. Write a PostgreSQL query to return each employee together with their direct manager, using a self-join on the employees table.

Requirements

Return only active employees whose manager_id is not NULL.
Show the employee's employee_id, employee_name, department, manager name, and a derived relationship_type.
Set relationship_type to Cross-Department when the employee and manager belong to different departments; otherwise set it to Same-Department.
Order the final result by manager name ascending, then employee name ascending.

Table Definitions

`employees`

column	type	description
employee_id	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department	VARCHAR(50)	Employee department
manager_id	INT	References another employee in the same table
employment_status	VARCHAR(20)	Current status such as Active, Leave, or Terminated
hire_date	DATE	Employee hire date

Sample Data

employee_id	employee_name	department	manager_id	employment_status	hire_date
101	Maya Chen	Executive	NULL	Active	2018-01-10
102	Leo Park	Engineering	101	Active	2019-03-15
103	Nina Shah	Engineering	102	Active	2021-06-01
104	Omar Ali	Sales	101	Active	2020-02-20
105	Priya Rao	Sales	104	Leave	2022-04-11
106	Quinn Bell	Finance	101	Active	2019-11-05
107	Rosa Diaz	Finance	104	Active	2023-01-09
108	Sam Wu	Engineering	102	Terminated	2021-09-30
109	Tara Singh	Sales	104	Active	2023-07-18
110	Uma Patel	HR	999	Active	2024-01-22

Expected Output

employee_id	employee_name	department	manager_name	relationship_type
103	Nina Shah	Engineering	Leo Park	Same-Department
107	Rosa Diaz	Finance	Omar Ali	Cross-Department
109	Tara Singh	Sales	Omar Ali	Same-Department
102	Leo Park	Engineering	Maya Chen	Cross-Department
104	Omar Ali	Sales	Maya Chen	Cross-Department
106	Quinn Bell	Finance	Maya Chen	Cross-Department

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department	VARCHAR(50)	Employee department
manager_id	INT	Manager employee_id stored in the same table
employment_status	VARCHAR(20)	Employment status such as Active, Leave, or Terminated
hire_date	DATE	Employee hire date

employees

employee_id	employee_name	department	manager_id	employment_status	hire_date
109	Tara Singh	Sales	104	Active	2023-07-18
103	Nina Shah	Engineering	102	Active	2021-06-01
101	Maya Chen	Executive		Active	2018-01-10
108	Sam Wu	Engineering	102	Terminated	2021-09-30
106	Quinn Bell	Finance	101	Active	2019-11-05
104	Omar Ali	Sales	101	Active	2020-02-20
110	Uma Patel	HR	999	Active	2024-01-22
107	Rosa Diaz	Finance	104	Active	2023-01-09
102	Leo Park	Engineering	101	Active	2019-03-15
105	Priya Rao	Sales	104	Leave	2022-04-11
1	Quinn Bell	Finance	53	Active	2022-11-02
2	Leo Park	Engineering	75	Active	2019-09-24
3	Leo Park	null	36	Active	2022-09-24
4	Uma Patel	Executive	10	Leave	2018-10-30
5	Leo Park	Sales	45	Active	2023-10-26
6	Quinn Bell	Engineering	23	Active	2018-11-19
7	Leo Park	Finance	60	Active	2022-03-17
8	Leo Park	HR	11	Active	2022-07-23
9	Omar Ali	Engineering	57	Active	2023-11-25
10	Rosa Diaz	null	14	Active	2019-01-11
11	Priya Rao	Sales	99	Active	2021-07-22
12	Uma Patel	Finance	1	Active	2022-03-17
13	Maya Chen	Sales	45	Active	2019-07-16
14	Quinn Bell	Engineering	5	Active	2021-12-02
15	Uma Patel	Sales	14	Active	2023-11-13
16	Sam Wu	Sales	16	Active	2023-09-10

Expected Output

employee_id	employee_name	department	manager_name	relationship_type
103	Nina Shah	Engineering	Leo Park	Same-Department
14	Quinn Bell	Engineering	Leo Park	Cross-Department
102	Leo Park	Engineering	Maya Chen	Cross-Department
104	Omar Ali	Sales	Maya Chen	Cross-Department
106	Quinn Bell	Finance	Maya Chen	Cross-Department
107	Rosa Diaz	Finance	Omar Ali	Cross-Department
109	Tara Singh	Sales	Omar Ali	Same-Department
8	Leo Park	HR	Priya Rao	Cross-Department
10	Rosa Diaz	null	Quinn Bell	Cross-Department
12	Uma Patel	Finance	Quinn Bell	Same-Department
15	Uma Patel	Sales	Quinn Bell	Cross-Department
16	Sam Wu	Sales	Sam Wu	Same-Department

Problem

Task

Requirements

Return only active employees whose manager_id is not NULL.
Show the employee's employee_id, employee_name, department, manager name, and a derived relationship_type.
Set relationship_type to Cross-Department when the employee and manager belong to different departments; otherwise set it to Same-Department.
Order the final result by manager name ascending, then employee name ascending.

Table Definitions

`employees`

column	type	description
employee_id	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department	VARCHAR(50)	Employee department
manager_id	INT	References another employee in the same table
employment_status	VARCHAR(20)	Current status such as Active, Leave, or Terminated
hire_date	DATE	Employee hire date

Sample Data

employee_id	employee_name	department	manager_id	employment_status	hire_date
101	Maya Chen	Executive	NULL	Active	2018-01-10
102	Leo Park	Engineering	101	Active	2019-03-15
103	Nina Shah	Engineering	102	Active	2021-06-01
104	Omar Ali	Sales	101	Active	2020-02-20
105	Priya Rao	Sales	104	Leave	2022-04-11
106	Quinn Bell	Finance	101	Active	2019-11-05
107	Rosa Diaz	Finance	104	Active	2023-01-09
108	Sam Wu	Engineering	102	Terminated	2021-09-30
109	Tara Singh	Sales	104	Active	2023-07-18
110	Uma Patel	HR	999	Active	2024-01-22

Expected Output

employee_id	employee_name	department	manager_name	relationship_type
103	Nina Shah	Engineering	Leo Park	Same-Department
107	Rosa Diaz	Finance	Omar Ali	Cross-Department
109	Tara Singh	Sales	Omar Ali	Same-Department
102	Leo Park	Engineering	Maya Chen	Cross-Department
104	Omar Ali	Sales	Maya Chen	Cross-Department
106	Quinn Bell	Finance	Maya Chen	Cross-Department

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department	VARCHAR(50)	Employee department
manager_id	INT	Manager employee_id stored in the same table
employment_status	VARCHAR(20)	Employment status such as Active, Leave, or Terminated
hire_date	DATE	Employee hire date

employees

employee_id	employee_name	department	manager_id	employment_status	hire_date
109	Tara Singh	Sales	104	Active	2023-07-18
103	Nina Shah	Engineering	102	Active	2021-06-01
101	Maya Chen	Executive		Active	2018-01-10
108	Sam Wu	Engineering	102	Terminated	2021-09-30
106	Quinn Bell	Finance	101	Active	2019-11-05
104	Omar Ali	Sales	101	Active	2020-02-20
110	Uma Patel	HR	999	Active	2024-01-22
107	Rosa Diaz	Finance	104	Active	2023-01-09
102	Leo Park	Engineering	101	Active	2019-03-15
105	Priya Rao	Sales	104	Leave	2022-04-11
1	Quinn Bell	Finance	53	Active	2022-11-02
2	Leo Park	Engineering	75	Active	2019-09-24
3	Leo Park	null	36	Active	2022-09-24
4	Uma Patel	Executive	10	Leave	2018-10-30
5	Leo Park	Sales	45	Active	2023-10-26
6	Quinn Bell	Engineering	23	Active	2018-11-19
7	Leo Park	Finance	60	Active	2022-03-17
8	Leo Park	HR	11	Active	2022-07-23
9	Omar Ali	Engineering	57	Active	2023-11-25
10	Rosa Diaz	null	14	Active	2019-01-11
11	Priya Rao	Sales	99	Active	2021-07-22
12	Uma Patel	Finance	1	Active	2022-03-17
13	Maya Chen	Sales	45	Active	2019-07-16
14	Quinn Bell	Engineering	5	Active	2021-12-02
15	Uma Patel	Sales	14	Active	2023-11-13
16	Sam Wu	Sales	16	Active	2023-09-10

Expected Output

employee_id	employee_name	department	manager_name	relationship_type
103	Nina Shah	Engineering	Leo Park	Same-Department
14	Quinn Bell	Engineering	Leo Park	Cross-Department
102	Leo Park	Engineering	Maya Chen	Cross-Department
104	Omar Ali	Sales	Maya Chen	Cross-Department
106	Quinn Bell	Finance	Maya Chen	Cross-Department
107	Rosa Diaz	Finance	Omar Ali	Cross-Department
109	Tara Singh	Sales	Omar Ali	Same-Department
8	Leo Park	HR	Priya Rao	Cross-Department
10	Rosa Diaz	null	Quinn Bell	Cross-Department
12	Uma Patel	Finance	Quinn Bell	Same-Department
15	Uma Patel	Sales	Quinn Bell	Cross-Department
16	Sam Wu	Sales	Sam Wu	Same-Department

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Employee Attrition Report by DepartmentMedium Analyze Employee Turnover Trends for Workforce PlanningMedium

Validate Workforce Data Across SystemsMedium

Next question

Find Employee Manager Relationships

Medium

SQL & Data Manipulation

JoinsCTEsSelf-Joins

Problem

Task

Requirements

Return only active employees whose manager_id is not NULL.
Show the employee's employee_id, employee_name, department, manager name, and a derived relationship_type.
Set relationship_type to Cross-Department when the employee and manager belong to different departments; otherwise set it to Same-Department.
Order the final result by manager name ascending, then employee name ascending.

Table Definitions

`employees`

column	type	description
employee_id	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department	VARCHAR(50)	Employee department
manager_id	INT	References another employee in the same table
employment_status	VARCHAR(20)	Current status such as Active, Leave, or Terminated
hire_date	DATE	Employee hire date

Sample Data

employee_id	employee_name	department	manager_id	employment_status	hire_date
101	Maya Chen	Executive	NULL	Active	2018-01-10
102	Leo Park	Engineering	101	Active	2019-03-15
103	Nina Shah	Engineering	102	Active	2021-06-01
104	Omar Ali	Sales	101	Active	2020-02-20
105	Priya Rao	Sales	104	Leave	2022-04-11
106	Quinn Bell	Finance	101	Active	2019-11-05
107	Rosa Diaz	Finance	104	Active	2023-01-09
108	Sam Wu	Engineering	102	Terminated	2021-09-30
109	Tara Singh	Sales	104	Active	2023-07-18
110	Uma Patel	HR	999	Active	2024-01-22

Expected Output

employee_id	employee_name	department	manager_name	relationship_type
103	Nina Shah	Engineering	Leo Park	Same-Department
107	Rosa Diaz	Finance	Omar Ali	Cross-Department
109	Tara Singh	Sales	Omar Ali	Same-Department
102	Leo Park	Engineering	Maya Chen	Cross-Department
104	Omar Ali	Sales	Maya Chen	Cross-Department
106	Quinn Bell	Finance	Maya Chen	Cross-Department

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department	VARCHAR(50)	Employee department
manager_id	INT	Manager employee_id stored in the same table
employment_status	VARCHAR(20)	Employment status such as Active, Leave, or Terminated
hire_date	DATE	Employee hire date

employees

employee_id	employee_name	department	manager_id	employment_status	hire_date
109	Tara Singh	Sales	104	Active	2023-07-18
103	Nina Shah	Engineering	102	Active	2021-06-01
101	Maya Chen	Executive		Active	2018-01-10
108	Sam Wu	Engineering	102	Terminated	2021-09-30
106	Quinn Bell	Finance	101	Active	2019-11-05
104	Omar Ali	Sales	101	Active	2020-02-20
110	Uma Patel	HR	999	Active	2024-01-22
107	Rosa Diaz	Finance	104	Active	2023-01-09
102	Leo Park	Engineering	101	Active	2019-03-15
105	Priya Rao	Sales	104	Leave	2022-04-11
1	Quinn Bell	Finance	53	Active	2022-11-02
2	Leo Park	Engineering	75	Active	2019-09-24
3	Leo Park	null	36	Active	2022-09-24
4	Uma Patel	Executive	10	Leave	2018-10-30
5	Leo Park	Sales	45	Active	2023-10-26
6	Quinn Bell	Engineering	23	Active	2018-11-19
7	Leo Park	Finance	60	Active	2022-03-17
8	Leo Park	HR	11	Active	2022-07-23
9	Omar Ali	Engineering	57	Active	2023-11-25
10	Rosa Diaz	null	14	Active	2019-01-11
11	Priya Rao	Sales	99	Active	2021-07-22
12	Uma Patel	Finance	1	Active	2022-03-17
13	Maya Chen	Sales	45	Active	2019-07-16
14	Quinn Bell	Engineering	5	Active	2021-12-02
15	Uma Patel	Sales	14	Active	2023-11-13
16	Sam Wu	Sales	16	Active	2023-09-10

Expected Output

employee_id	employee_name	department	manager_name	relationship_type
103	Nina Shah	Engineering	Leo Park	Same-Department
14	Quinn Bell	Engineering	Leo Park	Cross-Department
102	Leo Park	Engineering	Maya Chen	Cross-Department
104	Omar Ali	Sales	Maya Chen	Cross-Department
106	Quinn Bell	Finance	Maya Chen	Cross-Department
107	Rosa Diaz	Finance	Omar Ali	Cross-Department
109	Tara Singh	Sales	Omar Ali	Same-Department
8	Leo Park	HR	Priya Rao	Cross-Department
10	Rosa Diaz	null	Quinn Bell	Cross-Department
12	Uma Patel	Finance	Quinn Bell	Same-Department
15	Uma Patel	Sales	Quinn Bell	Cross-Department
16	Sam Wu	Sales	Sam Wu	Same-Department

Problem

Task

Requirements

Return only active employees whose manager_id is not NULL.
Show the employee's employee_id, employee_name, department, manager name, and a derived relationship_type.
Set relationship_type to Cross-Department when the employee and manager belong to different departments; otherwise set it to Same-Department.
Order the final result by manager name ascending, then employee name ascending.

Table Definitions

`employees`

column	type	description
employee_id	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department	VARCHAR(50)	Employee department
manager_id	INT	References another employee in the same table
employment_status	VARCHAR(20)	Current status such as Active, Leave, or Terminated
hire_date	DATE	Employee hire date

Sample Data

employee_id	employee_name	department	manager_id	employment_status	hire_date
101	Maya Chen	Executive	NULL	Active	2018-01-10
102	Leo Park	Engineering	101	Active	2019-03-15
103	Nina Shah	Engineering	102	Active	2021-06-01
104	Omar Ali	Sales	101	Active	2020-02-20
105	Priya Rao	Sales	104	Leave	2022-04-11
106	Quinn Bell	Finance	101	Active	2019-11-05
107	Rosa Diaz	Finance	104	Active	2023-01-09
108	Sam Wu	Engineering	102	Terminated	2021-09-30
109	Tara Singh	Sales	104	Active	2023-07-18
110	Uma Patel	HR	999	Active	2024-01-22

Expected Output

employee_id	employee_name	department	manager_name	relationship_type
103	Nina Shah	Engineering	Leo Park	Same-Department
107	Rosa Diaz	Finance	Omar Ali	Cross-Department
109	Tara Singh	Sales	Omar Ali	Same-Department
102	Leo Park	Engineering	Maya Chen	Cross-Department
104	Omar Ali	Sales	Maya Chen	Cross-Department
106	Quinn Bell	Finance	Maya Chen	Cross-Department

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department	VARCHAR(50)	Employee department
manager_id	INT	Manager employee_id stored in the same table
employment_status	VARCHAR(20)	Employment status such as Active, Leave, or Terminated
hire_date	DATE	Employee hire date

employees

employee_id	employee_name	department	manager_id	employment_status	hire_date
109	Tara Singh	Sales	104	Active	2023-07-18
103	Nina Shah	Engineering	102	Active	2021-06-01
101	Maya Chen	Executive		Active	2018-01-10
108	Sam Wu	Engineering	102	Terminated	2021-09-30
106	Quinn Bell	Finance	101	Active	2019-11-05
104	Omar Ali	Sales	101	Active	2020-02-20
110	Uma Patel	HR	999	Active	2024-01-22
107	Rosa Diaz	Finance	104	Active	2023-01-09
102	Leo Park	Engineering	101	Active	2019-03-15
105	Priya Rao	Sales	104	Leave	2022-04-11
1	Quinn Bell	Finance	53	Active	2022-11-02
2	Leo Park	Engineering	75	Active	2019-09-24
3	Leo Park	null	36	Active	2022-09-24
4	Uma Patel	Executive	10	Leave	2018-10-30
5	Leo Park	Sales	45	Active	2023-10-26
6	Quinn Bell	Engineering	23	Active	2018-11-19
7	Leo Park	Finance	60	Active	2022-03-17
8	Leo Park	HR	11	Active	2022-07-23
9	Omar Ali	Engineering	57	Active	2023-11-25
10	Rosa Diaz	null	14	Active	2019-01-11
11	Priya Rao	Sales	99	Active	2021-07-22
12	Uma Patel	Finance	1	Active	2022-03-17
13	Maya Chen	Sales	45	Active	2019-07-16
14	Quinn Bell	Engineering	5	Active	2021-12-02
15	Uma Patel	Sales	14	Active	2023-11-13
16	Sam Wu	Sales	16	Active	2023-09-10

Expected Output

employee_id	employee_name	department	manager_name	relationship_type
103	Nina Shah	Engineering	Leo Park	Same-Department
14	Quinn Bell	Engineering	Leo Park	Cross-Department
102	Leo Park	Engineering	Maya Chen	Cross-Department
104	Omar Ali	Sales	Maya Chen	Cross-Department
106	Quinn Bell	Finance	Maya Chen	Cross-Department
107	Rosa Diaz	Finance	Omar Ali	Cross-Department
109	Tara Singh	Sales	Omar Ali	Same-Department
8	Leo Park	HR	Priya Rao	Cross-Department
10	Rosa Diaz	null	Quinn Bell	Cross-Department
12	Uma Patel	Finance	Quinn Bell	Same-Department
15	Uma Patel	Sales	Quinn Bell	Cross-Department
16	Sam Wu	Sales	Sam Wu	Same-Department

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Employee Attrition Report by DepartmentMedium Analyze Employee Turnover Trends for Workforce PlanningMedium

Validate Workforce Data Across SystemsMedium

Next question