1378. Replace Employee ID With The Unique Identifier

Problem¶

Given employees and an optional mapping from employee id to unique_id, return every employee's name with their unique_id, or NULL if missing.

Input:

# Employees
---+---------
id | name
---+---------
1  | 'Alice'
7  | 'Bob'
3  | 'Jonathan'

# EmployeeUNI
---+----------
id | unique_id
---+----------
3  | 1

Output:

----------+-----------
unique_id | name
----------+-----------
NULL      | 'Alice'
NULL      | 'Bob'
1         | 'Jonathan'

Key trick¶

Use Employees as the left table in a LEFT JOIN, because every employee must appear even if they have no matching unique_id.

Trap¶

Using INNER JOIN drops employees without a unique_id.
Joining from EmployeeUNI to Employees keeps only mapped employees.
In Pandas, missing SQL NULL becomes NaN.

Why is it interesting?¶

It checks whether you understand the direction and semantics of a LEFT JOIN, which is one of the most common SQL interview patterns.

SQL solution¶

SELECT
    u.unique_id,
    e.name
FROM Employees AS e
LEFT JOIN EmployeeUNI AS u
    ON e.id = u.id;

PostgreSQL is identical, so no separate solution is needed.

Pandas solution¶

import pandas as pd

def replace_employee_id(
    employees: pd.DataFrame,
    employee_uni: pd.DataFrame,
) -> pd.DataFrame:
    # Left merge keeps all employees and fills missing unique_id with NaN.
    merged = employees.merge(employee_uni, on="id", how="left")

    # Return only the required columns.
    return merged[["unique_id", "name"]]

Pytest test¶

import sqlite3

import pandas as pd
import pytest


SQL_QUERY = """
SELECT
    u.unique_id,
    e.name
FROM Employees AS e
LEFT JOIN EmployeeUNI AS u
    ON e.id = u.id;
"""


def replace_employee_id(
    employees: pd.DataFrame,
    employee_uni: pd.DataFrame,
) -> pd.DataFrame:
    merged = employees.merge(employee_uni, on="id", how="left")
    return merged[["unique_id", "name"]]


def make_employees(rows):
    return pd.DataFrame(rows, columns=["id", "name"]).astype(
        {"id": "int64", "name": "object"}
    )


def make_employee_uni(rows):
    return pd.DataFrame(rows, columns=["id", "unique_id"]).astype(
        {"id": "int64", "unique_id": "int64"}
    )


def normalize_rows(rows):
    normalized = []

    for unique_id, name in rows:
        if pd.isna(unique_id):
            unique_id = None
        else:
            unique_id = int(unique_id)

        normalized.append((unique_id, name))

    return sorted(normalized, key=lambda row: (row[1], -1 if row[0] is None else row[0]))


@pytest.mark.parametrize(
    "employees_rows, employee_uni_rows, expected_rows",
    [
        (
            [(1, "Alice"), (7, "Bob"), (11, "Meir"), (90, "Winston"), (3, "Jonathan")],
            [(3, 1), (11, 2), (90, 3)],
            [(None, "Alice"), (None, "Bob"), (2, "Meir"), (3, "Winston"), (1, "Jonathan")],
        ),
        (
            [(1, "Alice"), (2, "Bob")],
            [],
            [(None, "Alice"), (None, "Bob")],
        ),
        (
            [(1, "Alice"), (2, "Bob")],
            [(1, 10), (2, 20)],
            [(10, "Alice"), (20, "Bob")],
        ),
        (
            [],
            [(1, 10)],
            [],
        ),
    ],
)
def test_replace_employee_id_sql_and_pandas(
    employees_rows,
    employee_uni_rows,
    expected_rows,
):
    employees = make_employees(employees_rows)
    employee_uni = make_employee_uni(employee_uni_rows)

    pandas_result = replace_employee_id(employees, employee_uni)
    pandas_rows = list(pandas_result.itertuples(index=False, name=None))

    assert normalize_rows(pandas_rows) == normalize_rows(expected_rows)

    con = sqlite3.connect(":memory:")

    con.execute("CREATE TABLE Employees (id INTEGER, name TEXT)")
    con.execute("CREATE TABLE EmployeeUNI (id INTEGER, unique_id INTEGER)")

    con.executemany(
        "INSERT INTO Employees (id, name) VALUES (?, ?)",
        employees_rows,
    )
    con.executemany(
        "INSERT INTO EmployeeUNI (id, unique_id) VALUES (?, ?)",
        employee_uni_rows,
    )

    sql_rows = con.execute(SQL_QUERY).fetchall()

    assert normalize_rows(sql_rows) == normalize_rows(expected_rows)

    con.close()

Comment on my solution¶

Your SQL and Pandas solution are correct.

SELECT
    uni.unique_id,
    e.name
FROM Employees AS e
LEFT JOIN EmployeeUNI AS uni
ON e.id = uni.id;

import pandas as pd

def replace_employee_id(employees: pd.DataFrame, employee_uni: pd.DataFrame) -> pd.DataFrame:
   tmp = employees.merge(employee_uni, left_on="id", right_on="id", how="left")
   return tmp[["unique_id", "name"]]

Minor notes:

on="id" is shorter than using both left_on="id" and right_on="id".
Missing unique_id values appear as NaN in Pandas, which corresponds to SQL NULL.