577. Employee Bonus

Problem¶

Return each employee's name and bonus when their bonus is below 1000, or when they have no bonus row.

Input:

# Employee
------+--------+------------+-------
empId | name   | supervisor | salary
------+--------+------------+-------
3     | Brad   | null       | 4000
1     | John   | 3          | 1000
2     | Dan    | 3          | 2000
4     | Thomas | 3          | 4000

# Bonus
------+------
empId | bonus
------+------
2     | 500
4     | 2000

Output:

-----+------
name | bonus
-----+------
Brad | null
John | null
Dan  | 500

Key trick¶

Use a LEFT JOIN from Employee to Bonus, then keep rows where bonus < 1000 or bonus IS NULL.

Trap¶

Using an INNER JOIN removes employees with no bonus.
Writing only bonus < 1000 misses NULL bonuses because comparisons with NULL are unknown.
Using <= 1000 is wrong because the condition is strictly less than 1000.
Putting bonus < 1000 only in the JOIN condition can incorrectly keep employees with bonuses >= 1000 as if they had no bonus.

Why is it interesting?¶

This is a small but classic test of LEFT JOIN semantics and NULL handling.

SQL solution¶

SELECT
    e.name,
    b.bonus
FROM Employee AS e
LEFT JOIN Bonus AS b
    ON e.empId = b.empId
-- Keep real low bonuses and employees without a matching bonus row.
WHERE b.bonus < 1000
   OR b.bonus IS NULL;

Pandas solution¶

import pandas as pd


def employee_bonus(employee: pd.DataFrame, bonus: pd.DataFrame) -> pd.DataFrame:
    return (
        employee
        # LEFT JOIN so employees without bonus are preserved.
        .merge(bonus, on="empId", how="left")
        # Keep bonus < 1000 or missing bonus.
        .loc[lambda df: (df["bonus"] < 1000) | df["bonus"].isna()]
        [["name", "bonus"]]
    )

Pytest test¶

import sqlite3

import pandas as pd
import pytest


SQLITE_QUERY = """
SELECT
    e.name,
    b.bonus
FROM Employee AS e
LEFT JOIN Bonus AS b
    ON e.empId = b.empId
WHERE b.bonus < 1000
   OR b.bonus IS NULL;
"""


def employee_bonus(employee: pd.DataFrame, bonus: pd.DataFrame) -> pd.DataFrame:
    return (
        employee
        .merge(bonus, on="empId", how="left")
        .loc[lambda df: (df["bonus"] < 1000) | df["bonus"].isna()]
        [["name", "bonus"]]
    )


def run_sqlite(employee_rows, bonus_rows):
    conn = sqlite3.connect(":memory:")

    conn.execute(
        """
        CREATE TABLE Employee (
            empId INTEGER,
            name TEXT,
            supervisor INTEGER,
            salary INTEGER
        )
        """
    )
    conn.execute(
        """
        CREATE TABLE Bonus (
            empId INTEGER,
            bonus INTEGER
        )
        """
    )

    conn.executemany(
        """
        INSERT INTO Employee (empId, name, supervisor, salary)
        VALUES (?, ?, ?, ?)
        """,
        employee_rows,
    )
    conn.executemany(
        """
        INSERT INTO Bonus (empId, bonus)
        VALUES (?, ?)
        """,
        bonus_rows,
    )

    return conn.execute(SQLITE_QUERY).fetchall()


def make_employee_df(rows):
    return pd.DataFrame(
        rows,
        columns=["empId", "name", "supervisor", "salary"],
    )


def make_bonus_df(rows):
    return pd.DataFrame(
        rows,
        columns=["empId", "bonus"],
    )


def normalize_rows(rows):
    return sorted(rows, key=lambda row: row[0])


def normalize_pandas_result(df):
    normalized = df.copy()
    normalized["bonus"] = normalized["bonus"].astype("object")
    normalized["bonus"] = normalized["bonus"].where(normalized["bonus"].notna(), None)
    return normalize_rows([tuple(row) for row in normalized[["name", "bonus"]].to_numpy()])


@pytest.mark.parametrize(
    "employee_rows, bonus_rows, expected",
    [
        (
            [
                (3, "Brad", None, 4000),
                (1, "John", 3, 1000),
                (2, "Dan", 3, 2000),
                (4, "Thomas", 3, 4000),
            ],
            [
                (2, 500),
                (4, 2000),
            ],
            [
                ("Brad", None),
                ("John", None),
                ("Dan", 500),
            ],
        ),
        (
            [
                (1, "A", None, 100),
                (2, "B", 1, 200),
            ],
            [],
            [
                ("A", None),
                ("B", None),
            ],
        ),
        (
            [
                (1, "Low", None, 100),
                (2, "Boundary", None, 100),
                (3, "High", None, 100),
            ],
            [
                (1, 999),
                (2, 1000),
                (3, 1001),
            ],
            [
                ("Low", 999),
            ],
        ),
        (
            [],
            [],
            [],
        ),
    ],
)
def test_employee_bonus_sqlite_and_pandas(employee_rows, bonus_rows, expected):
    sql_result = run_sqlite(employee_rows, bonus_rows)

    employee = make_employee_df(employee_rows)
    bonus = make_bonus_df(bonus_rows)
    pandas_result = employee_bonus(employee, bonus)

    assert normalize_rows(sql_result) == normalize_rows(expected)
    assert normalize_pandas_result(pandas_result) == normalize_rows(expected)

Comment on my solution¶

Your SQL solution is correct: it uses the required LEFT JOIN and handles missing bonuses with IS NULL.

Your Pandas solution is also correct: how="left" preserves employees without bonuses, and isna() correctly mirrors the SQL IS NULL condition.

SELECT
    e.name,
    b.bonus
FROM Employee AS e
LEFT JOIN Bonus AS b
       ON b.empId = e.empId
WHERE b.bonus < 1000 OR b.bonus IS NULL;

import pandas as pd

def employee_bonus(employee: pd.DataFrame, bonus: pd.DataFrame) -> pd.DataFrame:
    return (
        employee.merge(bonus, on="empId", how="left")
        .loc[lambda df: (df["bonus"] < 1000) | df["bonus"].isna()]
        [["name", "bonus"]]
    )