Interview Questions/SQL/Employee Whose Manager Left the Company

Employee Whose Manager Left the Company

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Employee Whose Manager Left the Company

You have a table of employees. Some employees have a manager (indicated by manager_id). If a manager leaves the company, their row is deleted from the table, but the employees they managed still have the old manager_id. Write a SQL query to find the IDs of employees who: 1. Earn less than $30,000, AND 2. Have a manager who is no longer in the company (i.e., their manager_id does not exist in the employee_id column). Return the result sorted by employee_id ascending.

Examples

Example 1

Joziah (id=11) earns 28485 < 30000 and his manager (id=6) is not in the table. Kalel (id=1) earns 21241 < 30000 but his manager (id=11, Joziah) is still in the table.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.

query.sql