Employee Whose Manager Left the Company

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

Medium

Description

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.

Database Schema

Employees

Column Name	Type	Description
employee_id	INT	Primary key
name	VARCHAR	Employee name
manager_id	INT	Manager's employee_id (nullable)
salary	INT	Annual salary

Example

Employees

employee_id	name	manager_id	salary
3	Mila	9	60301
12	Antonella	NULL	31000
13	Emery	NULL	67084
1	Kalel	11	21241
9	Mikaela	NULL	50937
11	Joziah	6	28485

Output

employee_id
11

Explanation:

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.