1280. Students and Examinations

Problem¶

Given all students, all subjects, and exam attendance rows that may contain duplicates, return every (student, subject) pair with how many times that student attended that subject exam, including 0, ordered by student_id, then subject_name.

Input:

# Students
-----------+-------------
student_id | student_name
-----------+-------------
1          | 'Alice'
2          | 'Bob'

# Subjects
------------
subject_name
------------
'Math'
'Physics'

# Examinations
-----------+-------------
student_id | subject_name
-----------+-------------
1          | 'Math'
1          | 'Math'
2          | 'Physics'

Output:

student_id | student_name | subject_name | attended_exams
-----------+--------------+--------------+---------------
1          | 'Alice'      | 'Math'       | 2
1          | 'Alice'      | 'Physics'    | 0
2          | 'Bob'        | 'Math'       | 0
2          | 'Bob'        | 'Physics'    | 1

Key trick¶

Build the complete student-subject grid with CROSS JOIN, then LEFT JOIN the attendance counts so missing exams become 0.

Trap¶

Counting after a LEFT JOIN with COUNT(*) or Pandas size() can incorrectly count missing matches as 1.
Count a non-null right-side column in SQL, or pre-aggregate Examinations in Pandas.
In Pandas, join key columns are not duplicated, so suffixes do not create a column like subject_name_exams.

Why is it interesting?¶

It tests whether you know how to preserve missing combinations while aggregating duplicate facts.

SQL solution¶

-- SQLite
WITH student_subjects AS (
    -- Generate every required output row first.
    SELECT
        s.student_id,
        s.student_name,
        sub.subject_name
    FROM Students AS s
    CROSS JOIN Subjects AS sub
)
SELECT
    ss.student_id,
    ss.student_name,
    ss.subject_name,
    -- COUNT(column_from_right_table) ignores NULLs from non-matches.
    COUNT(e.subject_name) AS attended_exams
FROM student_subjects AS ss
LEFT JOIN Examinations AS e
    ON e.student_id = ss.student_id
   AND e.subject_name = ss.subject_name
GROUP BY ss.student_id, ss.student_name, ss.subject_name
ORDER BY ss.student_id, ss.subject_name;

Pandas solution¶

import pandas as pd


def students_and_examinations(
    students: pd.DataFrame,
    subjects: pd.DataFrame,
    examinations: pd.DataFrame,
) -> pd.DataFrame:
    # Generate every required output row first.
    student_subjects = students.merge(subjects, how="cross")

    # Pre-aggregate attendance rows, preserving duplicate exam attendance.
    counts = (
        examinations
        .groupby(["student_id", "subject_name"])
        .size()
        .reset_index(name="attended_exams")
    )

    # Left join counts onto the full grid; missing counts are zero.
    result = (
        student_subjects
        .merge(counts, on=["student_id", "subject_name"], how="left")
        .assign(attended_exams=lambda df: df["attended_exams"].fillna(0).astype("int64"))
        .sort_values(["student_id", "subject_name"])
        .reset_index(drop=True)
    )

    return result[
        ["student_id", "student_name", "subject_name", "attended_exams"]
    ]

Pytest test¶

import sqlite3

import pandas as pd
import pytest
from pandas.testing import assert_frame_equal


SQL_QUERY = """
WITH student_subjects AS (
    SELECT
        s.student_id,
        s.student_name,
        sub.subject_name
    FROM Students AS s
    CROSS JOIN Subjects AS sub
)
SELECT
    ss.student_id,
    ss.student_name,
    ss.subject_name,
    COUNT(e.subject_name) AS attended_exams
FROM student_subjects AS ss
LEFT JOIN Examinations AS e
    ON e.student_id = ss.student_id
   AND e.subject_name = ss.subject_name
GROUP BY
    ss.student_id,
    ss.student_name,
    ss.subject_name
ORDER BY
    ss.student_id,
    ss.subject_name;
"""


def pandas_solution(
    students: pd.DataFrame,
    subjects: pd.DataFrame,
    examinations: pd.DataFrame,
) -> pd.DataFrame:
    student_subjects = students.merge(subjects, how="cross")

    counts = (
        examinations
        .groupby(["student_id", "subject_name"])
        .size()
        .reset_index(name="attended_exams")
    )

    return (
        student_subjects
        .merge(counts, on=["student_id", "subject_name"], how="left")
        .assign(attended_exams=lambda df: df["attended_exams"].fillna(0).astype("int64"))
        .sort_values(["student_id", "subject_name"])
        .reset_index(drop=True)
        [["student_id", "student_name", "subject_name", "attended_exams"]]
    )


@pytest.mark.parametrize(
    "students_rows, subjects_rows, examinations_rows, expected_rows",
    [
        (
            [(1, "Alice"), (2, "Bob")],
            [("Math",), ("Physics",)],
            [(1, "Math"), (1, "Math"), (2, "Physics")],
            [
                (1, "Alice", "Math", 2),
                (1, "Alice", "Physics", 0),
                (2, "Bob", "Math", 0),
                (2, "Bob", "Physics", 1),
            ],
        ),
        (
            [(1, "Alice"), (2, "Bob"), (6, "Alex"), (13, "John")],
            [("Math",), ("Physics",), ("Programming",)],
            [
                (1, "Math"),
                (1, "Physics"),
                (1, "Programming"),
                (2, "Programming"),
                (1, "Physics"),
                (1, "Math"),
                (13, "Math"),
                (13, "Programming"),
                (13, "Physics"),
                (2, "Math"),
                (1, "Math"),
            ],
            [
                (1, "Alice", "Math", 3),
                (1, "Alice", "Physics", 2),
                (1, "Alice", "Programming", 1),
                (2, "Bob", "Math", 1),
                (2, "Bob", "Physics", 0),
                (2, "Bob", "Programming", 1),
                (6, "Alex", "Math", 0),
                (6, "Alex", "Physics", 0),
                (6, "Alex", "Programming", 0),
                (13, "John", "Math", 1),
                (13, "John", "Physics", 1),
                (13, "John", "Programming", 1),
            ],
        ),
        (
            [(10, "Nina")],
            [("Art",), ("Math",)],
            [],
            [
                (10, "Nina", "Art", 0),
                (10, "Nina", "Math", 0),
            ],
        ),
    ],
)
def test_students_and_examinations_sql_and_pandas(
    students_rows,
    subjects_rows,
    examinations_rows,
    expected_rows,
):
    expected = pd.DataFrame(
        expected_rows,
        columns=["student_id", "student_name", "subject_name", "attended_exams"],
    )

    conn = sqlite3.connect(":memory:")
    conn.execute("CREATE TABLE Students (student_id INTEGER, student_name TEXT)")
    conn.execute("CREATE TABLE Subjects (subject_name TEXT)")
    conn.execute("CREATE TABLE Examinations (student_id INTEGER, subject_name TEXT)")

    conn.executemany(
        "INSERT INTO Students (student_id, student_name) VALUES (?, ?)",
        students_rows,
    )
    conn.executemany(
        "INSERT INTO Subjects (subject_name) VALUES (?)",
        subjects_rows,
    )
    conn.executemany(
        "INSERT INTO Examinations (student_id, subject_name) VALUES (?, ?)",
        examinations_rows,
    )

    sql_result = pd.read_sql_query(SQL_QUERY, conn)
    assert_frame_equal(sql_result, expected, check_dtype=False)

    students = pd.DataFrame(
        students_rows,
        columns=["student_id", "student_name"],
    )
    subjects = pd.DataFrame(
        subjects_rows,
        columns=["subject_name"],
    )
    examinations = pd.DataFrame(
        examinations_rows,
        columns=["student_id", "subject_name"],
    )

    pandas_result = pandas_solution(students, subjects, examinations)
    assert_frame_equal(pandas_result, expected, check_dtype=False)

Comment on my solution¶

Your SQL solution is correct and uses the right pattern: CROSS JOIN to build the full grid, then LEFT JOIN and COUNT(e.subject_name) to count only real matches.

Your Pandas error happens because subject_name is a join key, so Pandas keeps only one copy of it and does not create subject_name_exams.

Also, using size() after the left join would count unmatched rows as 1; pre-aggregate examinations first or add a non-null marker column and sum it.

-- WORKS
WITH students_subjects AS (
    SELECT *
    FROM Students
    CROSS JOIN Subjects
)
SELECT
    s.student_id,
    s.student_name,
    s.subject_name,
    COUNT(e.subject_name) AS attended_exams
FROM students_subjects AS s
LEFT JOIN Examinations AS e
    ON e.student_id = s.student_id
   AND e.subject_name = s.subject_name
GROUP BY s.student_id, s.student_name, s.subject_name
ORDER BY s.student_id, s.subject_name;

import pandas as pd

# Error: Column(s) ['subject_name_exams'] do not exist
def students_and_examinations(students: pd.DataFrame, subjects: pd.DataFrame, examinations: pd.DataFrame) -> pd.DataFrame:
    students_subjects = students.merge(
        subjects,
        how="cross"
    )

    result = students_subjects.merge(
        examinations,
        on=["student_id", "subject_name"],
        how="left",
        suffixes=("", "_exams")
    )
    result = (
        result.groupby(["student_id", "student_name", "subject_name"])
        .agg(attended_exams=("subject_name_exams","size"))
    )

    return result