619. Biggest Single Number

Problem¶

Given table MyNumbers(num), return the largest value that appears exactly once. If no value appears exactly once, return null.

Input:

MyNumbers
---
num
---
8
8
3
3
1
4
5
6

Output:

---
num
---
6

Key trick¶

Group by num, keep only groups with count 1, then apply MAX.
MAX over an empty result returns null in SQL, which matches the requirement.

Trap¶

Returning all single numbers instead of only the largest one.
Using ORDER BY num DESC LIMIT 1 without handling the no-result case in SQL.
In Pandas, forgetting that an empty max should become a one-row dataframe with null, not an empty dataframe.

Why is it interesting?¶

It tests aggregation twice:
- First to identify values that appear once.
- Then to reduce those candidates to one answer.
It also tests correct null behavior when no candidates exist.

SQL solution¶

-- SQLite
-- First find numbers appearing exactly once, then take the maximum.
SELECT
    MAX(num) AS num
FROM (
    SELECT
        num
    FROM MyNumbers
    GROUP BY num
    HAVING COUNT(*) = 1
) AS single_numbers;

Pandas solution¶

import pandas as pd


def biggest_single_number(my_numbers: pd.DataFrame) -> pd.DataFrame:
    # Count occurrences of each number.
    counts = my_numbers.groupby("num", dropna=True).size()

    # Keep only numbers that appear exactly once.
    single_numbers = counts[counts.eq(1)].index

    # Return one row; use null if there is no single number.
    answer = single_numbers.max() if len(single_numbers) else pd.NA

    return pd.DataFrame({"num": pd.Series([answer], dtype="Int64")})

data = [[8], [8], [3], [3], [1], [4], [5], [6]]
my_numbers = pd.DataFrame(data, columns=['num']).astype({'num':'Int64'})

# .size() returns the number of rows in each group
# - as a Series if as_index is True
# - or a DataFrame if as_index is False.
counts = my_numbers.groupby("num", dropna=True).size()
type(counts)
# <class 'pandas.Series'>
counts
# num
# 1    1
# 3    2
# 4    1
# 5    1
# 6    1
# 8    2
# dtype: int64

counts.eq(1)
# num
# 1     True
# 3    False
# 4     True
# 5     True
# 6     True
# 8    False
# dtype: bool

counts[counts.eq(1)].index
# Index([1, 4, 5, 6], dtype='Int64', name='num')
type(counts[counts.eq(1)].index)
# <class 'pandas.Index'>

Pytest test¶

import sqlite3

import pandas as pd
import pytest


SQL_QUERY = """
SELECT
    MAX(num) AS num
FROM (
    SELECT
        num
    FROM MyNumbers
    GROUP BY num
    HAVING COUNT(*) = 1
) AS single_numbers;
"""


def biggest_single_number(my_numbers: pd.DataFrame) -> pd.DataFrame:
    # Count occurrences of each number.
    counts = my_numbers.groupby("num", dropna=True).size()

    # Keep only numbers that appear exactly once.
    single_numbers = counts[counts.eq(1)].index

    # Return one row; use null if there is no single number.
    answer = single_numbers.max() if len(single_numbers) else pd.NA

    return pd.DataFrame({"num": pd.Series([answer], dtype="Int64")})


def run_sql(values):
    conn = sqlite3.connect(":memory:")
    cur = conn.cursor()

    cur.execute("CREATE TABLE MyNumbers (num INTEGER)")

    if values:
        cur.executemany(
            "INSERT INTO MyNumbers (num) VALUES (?)",
            [(value,) for value in values],
        )

    cur.execute(SQL_QUERY)
    result = cur.fetchone()[0]

    conn.close()
    return result


def run_pandas(values):
    df = pd.DataFrame({"num": pd.Series(values, dtype="Int64")})
    return biggest_single_number(df).iloc[0]["num"]


def assert_same_nullable(actual, expected):
    if expected is None:
        assert pd.isna(actual)
    else:
        assert actual == expected


@pytest.mark.parametrize(
    ("values", "expected"),
    [
        ([8, 8, 3, 3, 1, 4, 5, 6], 6),
        ([8, 8, 7, 7, 3, 3, 3], None),
        ([-1, -1, -2, 0, 0], -2),
        ([2, 10, -5], 10),
        ([7], 7),
        ([], None),
    ],
)
def test_biggest_single_number_sql_and_pandas(values, expected):
    assert_same_nullable(run_sql(values), expected)
    assert_same_nullable(run_pandas(values), expected)

Comment on my solution¶

Your SQL solution is the standard clean answer.
Your Pandas solution is also correct and readable.
Small improvement:
- Explicitly return a nullable integer dtype so the no-single case is consistently represented as pd.NA instead of depending on Pandas inference.

SELECT
    MAX(num) AS num
FROM (
    SELECT num
    FROM MyNumbers
    GROUP BY num
    HAVING COUNT(*) = 1
);

import pandas as pd

def biggest_single_number(my_numbers: pd.DataFrame) -> pd.DataFrame:
    single_numbers = (
        my_numbers.groupby("num", as_index=False)
        .size()
        .loc[lambda df: df["size"] == 1]
        ["num"]
    )
    return pd.DataFrame({"num": [single_numbers.max()]})