Join Employee and Customer by Employee No | Dataford Interview Questions

Iss Governance needs a roster of active employees and the customers assigned to each employee. Write a PostgreSQL query that joins employees to customers using employee_number.

Requirements

Return only employees whose employment_status is Active.
Preserve active employees who have no matching customers by using a LEFT JOIN.
Return the employee number, employee name, department, number of assigned customers, and an assignment status of Assigned or No customers.
Sort the output by employee number in ascending order.

Schema

employees

Column	Type	Description
employee_numberPK	INTEGER	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department	VARCHAR(80)	Employee department
employment_status	VARCHAR(20)	Employment status, such as Active or Inactive

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(120)	Customer organization name
employee_number	INTEGER	Assigned employee number
customer_segment	VARCHAR(40)	Customer classification
account_status	VARCHAR(20)	Customer account status

Your question is Join Employee and Customer by Employee No. 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