Find Employee Manager Relationships | Dataford Interview Questions

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.

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

Your question is Find Employee Manager Relationships. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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.

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

Your question is Find Employee Manager Relationships. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.