Employee and Customer Join | Dataford Interview Questions

Your question is Employee and Customer Join. Start with the requirements and the two 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

ISS STOXX uses employee numbers to identify account ownership in its customer operations data. Write a PostgreSQL query that joins the employee and customer tables to show active customer assignments while retaining employees who have no active customer.

Requirements

Use a LEFT JOIN on employee.employee_no = customer.employee_no so every employee remains in the result.
Return only customers whose status is Active, without removing employees who have no active customer.
Include employee details, customer details, and an assignment_status column showing Active customer or No active customer.
Order the results by employee number and then customer ID, placing missing customer IDs last.

Schema

employee

Column	Type	Description
employee_noPK	INTEGER	Unique ISS STOXX employee number
employee_name	VARCHAR(100)	Employee's full name
team	VARCHAR(80)	Employee's operations team
office	VARCHAR(80)	Employee's office location

customer

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(120)	Customer organization name
employee_no	INTEGER	Assigned employee number
status	VARCHAR(20)	Customer assignment status