Problem

Table: Employees

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

+-------------+----------+
| Column Name | Type |
+-------------+----------+
| employee_id | int |
| name | varchar |
| manager_id | int |
| salary | int |
+-------------+----------+
In SQL, employee_id is the primary key for this table.
This table contains information about the employees, their salary, and the ID of their manager. Some employees do not have a manager (manager_id is null).

Find the IDs of the employees whose salary is strictly less than $30000 and whose manager left the company. When a manager leaves the company, their information is deleted from the Employees table, but the reports still have their manager_id set to the manager that left.

Return the result table ordered by employee_id.

The result format is in the following example.

Examples

Example 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

Input: 
Employees table:
+-------------+-----------+------------+--------+
| 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: 
The employees with a salary less than $30000 are 1 (Kalel) and 11 (Joziah).
Kalel's manager is employee 11, who is still in the company (Joziah).
Joziah's manager is employee 6, who left the company because there is no row for employee 6 as it was deleted.

Solution

Method 1 – Self Join and Null Check (SQL, Pandas)

Intuition

To find employees whose manager left, look for employees whose manager_id does not exist in the employee_id column. Combine this with the salary condition.

Approach

  1. Select employees with salary < 30000.
  2. For each, check if their manager_id is not present in the employee_id column (i.e., manager left).
  3. Return their employee_id ordered ascending.

Code

1
2
3
4
5
6

SELECT employee_id
FROM Employees e
WHERE salary < 30000
  AND manager_id IS NOT NULL
  AND manager_id NOT IN (SELECT employee_id FROM Employees)
ORDER BY employee_id;
1
2
3
4
5
6

SELECT employee_id
FROM Employees e
WHERE salary < 30000
  AND manager_id IS NOT NULL
  AND manager_id NOT IN (SELECT employee_id FROM Employees)
ORDER BY employee_id;
1
2
3
4
5
6
7
8

import pandas as pd

def employees_whose_manager_left(employees: pd.DataFrame) -> pd.DataFrame:
    ids = set(employees['employee_id'])
    res = employees[(employees['salary'] < 30000) &
                    (employees['manager_id'].notnull()) &
                    (~employees['manager_id'].isin(ids))][['employee_id']]
    return res.sort_values('employee_id')

Complexity

  • ⏰ Time complexity: O(n) where n is the number of employees.
  • 🧺 Space complexity: O(n) for storing employee ids.