Employees Reporting to One Manager | Dataford Interview Questions

Business Context

Health Catalyst uses employee reporting data to support organizational views in its Data Operating System. An engineering lead needs a list of everyone who reports directly to one manager.

Task

Write a PostgreSQL query that returns all direct reports for the manager whose employee_id is supplied as parameter \$1. Do not include the manager in the result.

Requirements

Use a self-join on employees to match each employee to the requested manager.
Return the employee ID, name, job title, department name, and manager name.
Use a LEFT JOIN for department names so an employee with no department still appears.
Sort results alphabetically by employee name, then by employee ID.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
job_title	VARCHAR(100)	Employee job title
manager_id	INT	Direct manager employee ID
department_id	INT	Employee department identifier

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name

Your question is Employees Reporting to One Manager. Start with the requirements and the two tables 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