Employees Whose Manager Left the Company

Employees Whose Manager Left the Company

What is this problem about?

The Employees Whose Manager Left the Company interview question asks you to find the IDs of employees who satisfy two conditions: their salary is less than $30,000, and their manager has left the company. An employee's manager has left the company if the manager_id provided in the employee's record no longer exists in the Employee table. The results should be ordered by employee ID.

Why is this asked in interviews?

Companies like Meta and Bloomberg ask this to test a candidate's ability to handle orphaned records and perform negative filtering in SQL. It evaluates knowledge of subqueries (using NOT IN) or LEFT JOIN operations to identify missing relationships. It’s a common data integrity check used in backend systems to clean up pointers to deleted entities.

Algorithmic pattern used

This is a Relational Filtering problem.

1. Filter for employees with salary < 30000.
2. Use a WHERE manager_id NOT IN (SELECT employee_id FROM Employees) clause to find those whose managers are missing.
3. Alternatively, use a LEFT JOIN between the employee table and itself and filter where the right side (the manager) is NULL but the manager_id was originally provided.
4. Add an ORDER BY employee_id clause.

Example explanation

Employees:

• ID 1: Salary 40k, Manager 3
• ID 2: Salary 25k, Manager 4
• ID 3: Salary 50k, Manager NULL
• (Manager 4 is not in the table anymore)

1. Check salaries: Only ID 2 is below 30k.
2. Check ID 2's manager (ID 4): ID 4 is not in the list of employee IDs.
3. Result: ID 2.

Common mistakes candidates make

• Ignoring NULL Manager IDs: Forgetting that NOT IN with a NULL can behave unexpectedly in some SQL environments. It’s safer to ensure the manager_id is not null before checking existence.
• Wrong Join Type: Using an INNER JOIN, which would discard the orphaned records you are actually looking for.
• Salary threshold error: Using <= 30000 instead of < 30000.

Interview preparation tip

Be careful with NOT IN if the subquery can return NULL values. A safer alternative is often NOT EXISTS or a LEFT JOIN ... WHERE right.id IS NULL. This is a classic SQL nuance that interviewers look for.