1978. Employees Whose Manager Left the Company

Problem¶

Given an Employees table, return employee IDs for employees who:

Have salary < 30000.
Have a non-null manager_id.
Reference a manager ID that no longer exists in Employees.

Return IDs sorted ascending.

Input:

# Employees
------------+------------+-------
employee_id | manager_id | salary
------------+------------+-------
1           | 11         | 21241
11          | 6          | 28485
12          | null       | 31000

Output:

-----------
employee_id
-----------
11

Key trick¶

Use an anti-join:

Keep low-salary employees.
Exclude rows with manager_id IS NULL.
Keep rows whose manager_id is not found among existing employee_id values.

Trap¶

In SQL, NOT IN can behave badly if the subquery can contain NULL.
In Pandas, use .isin(...); Python in does not compare a Series element-wise.
Employees with manager_id = NULL do not count as having a manager who left.

Why is it interesting?¶

It tests the classic missing-reference pattern, which appears often in data quality checks and orphan-record detection.

SQL solution¶

-- SQLite
SELECT
    e.employee_id
FROM Employees AS e
WHERE e.salary < 30000
  -- Employees without managers are not "managed by someone who left".
  AND e.manager_id IS NOT NULL
  -- Anti-join: no current employee has this manager ID.
  AND NOT EXISTS (
      SELECT 1
      FROM Employees AS m
      WHERE m.employee_id = e.manager_id
  )
ORDER BY e.employee_id;

Pandas solution¶

import pandas as pd

def find_employees(employees: pd.DataFrame) -> pd.DataFrame:
    # Existing employee IDs are the managers still in the company.
    existing_ids = employees["employee_id"]

    mask = (
        (employees["salary"] < 30000)
        & employees["manager_id"].notna()
        & ~employees["manager_id"].isin(existing_ids)
    )

    return (
        employees.loc[mask, ["employee_id"]]
        .sort_values("employee_id")
        .reset_index(drop=True)
    )

Pytest test¶

import sqlite3

import pandas as pd
import pytest


SQL_QUERY = """
SELECT
    e.employee_id
FROM Employees AS e
WHERE e.salary < 30000
  AND e.manager_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM Employees AS m
      WHERE m.employee_id = e.manager_id
  )
ORDER BY e.employee_id;
"""


def find_employees(employees: pd.DataFrame) -> pd.DataFrame:
    existing_ids = employees["employee_id"]

    mask = (
        (employees["salary"] < 30000)
        & employees["manager_id"].notna()
        & ~employees["manager_id"].isin(existing_ids)
    )

    return (
        employees.loc[mask, ["employee_id"]]
        .sort_values("employee_id")
        .reset_index(drop=True)
    )


@pytest.mark.parametrize(
    "rows, expected",
    [
        (
            [
                [3, "Mila", 9, 60301],
                [12, "Antonella", None, 31000],
                [13, "Emery", None, 67084],
                [1, "Kalel", 11, 21241],
                [9, "Mikaela", None, 50937],
                [11, "Joziah", 6, 28485],
            ],
            [11],
        ),
        (
            [
                [1, "A", None, 10000],
                [2, "B", 1, 10000],
                [3, "C", 9, 10000],
            ],
            [3],
        ),
        (
            [
                [1, "A", 9, 30000],
                [2, "B", 9, 29999],
                [3, "C", None, 10000],
            ],
            [2],
        ),
        (
            [
                [1, "A", 2, 10000],
                [2, "B", None, 10000],
                [3, "C", 2, 50000],
            ],
            [],
        ),
        (
            [
                [4, "D", 99, 10000],
                [2, "B", 88, 10000],
                [3, "C", 2, 10000],
            ],
            [2, 4],
        ),
    ],
)
def test_find_employees_sql_and_pandas(rows, expected):
    employees = pd.DataFrame(
        rows,
        columns=["employee_id", "name", "manager_id", "salary"],
    ).astype(
        {
            "employee_id": "Int64",
            "name": "object",
            "manager_id": "Int64",
            "salary": "Int64",
        }
    )

    pandas_result = find_employees(employees)
    assert pandas_result["employee_id"].tolist() == expected

    sqlite_df = employees.astype(object).where(pd.notna(employees), None)

    with sqlite3.connect(":memory:") as conn:
        sqlite_df.to_sql("Employees", conn, index=False, if_exists="replace")
        sql_result = pd.read_sql_query(SQL_QUERY, conn)

    assert sql_result["employee_id"].tolist() == expected

Comment on my solution¶

Your SQL solution is accepted under the LeetCode schema because employee_id is non-null, but NOT EXISTS is safer and more idiomatic for anti-joins.

Your Pandas issue is here:

employees["manager_id"] in employees["employee_id"]

That is not element-wise membership; use .isin(...) instead, and invert it because you want managers who are missing.

SELECT
    employee_id
FROM Employees
WHERE salary < 30000
  AND manager_id NOT IN (
         SELECT employee_id FROM Employees
      )
ORDER BY employee_id;

import pandas as pd

# DON'T WORK
# Error due to (employees["manager_id"] in employees["employee_id"])
def find_employees(employees: pd.DataFrame) -> pd.DataFrame:
    mask = (employees["salary"] < 30000) & (employees["manager_id"] in employees["employee_id"])
    return employees.loc[mask, ["employee_id"]].sort_values("employee_id")