SQL Joins for Calibration Logs | Dataford Interview Questions

Your question is SQL Joins for Calibration Logs. Start with the requirements and the three tables on the right.

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

Problem

Hexagon Ab needs a staffing view for active products such as HxDR and HxGN EAM. Write a PostgreSQL query that lists every active project, assigned employees, and each employee's manager.

Requirements

Use a RIGHT OUTER JOIN so active projects remain in the result even when they have no staffing assignment.
Use LEFT OUTER JOIN to retain assignments whose employee is currently unknown.
Use a self-join on employees to resolve each employee's manager.
Return project code, project name, employee name, manager name, and allocation percentage, ordered by project code and employee name, with unassigned rows last.

Schema

employees

Column	Type	Description
employee_idPK	INT	Employee identifier
employee_name	VARCHAR(100)	Employee's full name
manager_id	INT	Employee ID of the manager
department	VARCHAR(80)	Employee department

employee_projects

Column	Type	Description
assignment_idPK	INT	Assignment identifier
employee_id	INT	Assigned employee
project_id	INT	Assigned project
allocation_pct	INT	Percentage of working time allocated

projects

Column	Type	Description
project_idPK	INT	Project identifier
project_code	VARCHAR(30)	Project or product code
project_name	VARCHAR(150)	Project or product name
status	VARCHAR(20)	Project lifecycle status