Employees Whose Manager Left the Company - Practice Coding Problems

Employees Whose Manager Left the Company - Problem

Database Easy

Find Employees Whose Manager Left the Company

You're working with a company's HR database and need to identify employees who are in a problematic situation: their salary is less than $30,000 AND their manager has left the company.

The Employees table contains information about all employees including their salary and manager ID. When a manager leaves the company, their record is completely deleted from the table, but their former direct reports still have the departed manager's ID in their manager_id field.

Your task: Find the employee IDs of all employees who:
1. Earn strictly less than $30,000
2. Have a manager_id that doesn't exist in the current employee table (meaning their manager left)

Return the results ordered by employee_id in ascending order.

Note: Some employees may not have a manager at all (manager_id is null) - these should not be included in the results.

Input & Output

example_1.sql — Basic Example

$ Input: Employees table: +-------------+---------+------------+--------+ | employee_id | name | manager_id | salary | +-------------+---------+------------+--------+ | 3 | Mila | 9 | 60301 | | 12 | Antonella| 9 | 31000 | | 13 | Emery | 9 | 67084 | | 1 | Kalel | 11 | 21241 | | 9 | Mikaela | null | 50937 | | 11 | Joziah | 6 | 28485 | +-------------+---------+------------+--------+

› Output: +-------------+ | employee_id | +-------------+ | 11 | +-------------+

💡 Note: Employee 11 has salary 28485 < 30000 and manager_id = 6. Manager with ID 6 doesn't exist in the table, so employee 11's manager left the company. Employee 1 also has salary < 30000 but their manager (ID 11) still exists in the table.

example_2.sql — Multiple Results

$ Input: Employees table: +-------------+---------+------------+--------+ | employee_id | name | manager_id | salary | +-------------+---------+------------+--------+ | 1 | Alice | 5 | 25000 | | 2 | Bob | 5 | 28000 | | 3 | Carol | 1 | 35000 | | 4 | David | 6 | 22000 | +-------------+---------+------------+--------+

› Output: +-------------+ | employee_id | +-------------+ | 1 | | 2 | | 4 | +-------------+

💡 Note: Employees 1, 2, and 4 all have salaries < 30000. Employee 1 and 2 have manager_id = 5 which doesn't exist (manager left). Employee 4 has manager_id = 6 which also doesn't exist. Employee 3 has salary ≥ 30000 so is excluded.

example_3.sql — Edge Cases

$ Input: Employees table: +-------------+---------+------------+--------+ | employee_id | name | manager_id | salary | +-------------+---------+------------+--------+ | 1 | John | null | 25000 | | 2 | Jane | 1 | 28000 | | 3 | Mike | 7 | 45000 | +-------------+---------+------------+--------+

› Output: +-------------+ | employee_id | +-------------+ +-------------+

💡 Note: No results returned. Employee 1 has salary < 30000 but manager_id is null (no manager). Employee 2 has salary < 30000 and manager_id = 1, but employee 1 exists in the table. Employee 3 has salary ≥ 30000.

Constraints

1 ≤ employee_id ≤ 10⁴
1 ≤ salary ≤ 10⁶
manager_id can be null or reference another employee_id
employee_id is unique (primary key)
All salary values are positive integers

Visualization

Tap to expand

Understanding the Visualization

Identify Low Earners

Find all employees earning less than $30,000

Check Manager Status

For each low earner, verify if their manager still exists in the company

Find Orphaned Employees

Return IDs of employees whose managers have left (missing from employee table)

Key Takeaway

🎯 Key Insight: LEFT JOIN efficiently identifies missing relationships by producing NULL values where the join condition fails, making it perfect for finding orphaned employees whose managers have left the company.

Asked in

a Amazon 15 ⊞ Microsoft 12 f Meta 8 G Google 6

The optimal solution uses a LEFT JOIN to efficiently identify employees whose managers have left the company. By joining the Employees table with itself and checking for NULL values in the joined result, we can find orphaned employees in O(n log n) time. The key insight is leveraging SQL's built-in join optimizations rather than using expensive correlated subqueries.

Common Approaches

Approach	Time	Space	Notes
✓ LEFT JOIN (Optimal SQL Solution)	O(n log n)	O(n)	Use LEFT JOIN to efficiently find employees whose managers don't exist
Brute Force (Subquery for Each Row)	O(n²)	O(1)	Check each employee individually with a correlated subquery

LEFT JOIN (Optimal SQL Solution) — Algorithm Steps

LEFT JOIN Employees table with itself on employee_id = manager_id
Filter for salary < 30000 and non-null manager_id
Check WHERE the joined manager record IS NULL (manager doesn't exist)
Order results by employee_id

Visualization

Tap to expand

Step-by-Step Walkthrough

LEFT JOIN Setup

Join employees table with itself on manager relationship

Identify Orphans

Find rows where manager side of join is NULL

Apply Filters

Filter by salary and return employee IDs

Code -

solution.c — C

// Optimal SQL solution using LEFT JOIN
#include <stdio.h>
#include <string.h>

char* solution() {
    static char sql[] = """
        SELECT e1.employee_id
        FROM Employees e1
        LEFT JOIN Employees e2 ON e1.manager_id = e2.employee_id
        WHERE e1.salary < 30000 
          AND e1.manager_id IS NOT NULL
          AND e2.employee_id IS NULL
        ORDER BY e1.employee_id;
    """;
    return sql;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

LEFT JOIN typically uses hash join or sort-merge join, both optimized by database engines

⚡ Linearithmic

Space Complexity

O(n)

Database may create temporary hash table or sorted intermediate results for the join

⚡ Linearithmic Space

42.0K Views

Medium Frequency

~15 min Avg. Time

1.5K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

LEFT JOIN (Optimal SQL Solution) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler