Interview Guides

Find Employee Manager Relationships

Medium

SQL & Data Manipulation

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

Find Employee Manager Relationships

Medium

SQL & Data Manipulation

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

Practice SQL

PostgreSQL

Find Employee Manager Relationships

Medium

SQL & Data Manipulation

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

Find Employee Manager Relationships

Medium

SQL & Data Manipulation

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

Practice SQL

PostgreSQL