LeetCode 1280 - Students and Examinations

This problem asks us to generate a complete report showing how many times every student attended every subject examinati

leetcodeeasydatabase

LeetCode Problem 1280

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

This problem asks us to generate a complete report showing how many times every student attended every subject examination.

We are given three database tables:

  • Students, which stores each student's ID and name.
  • Subjects, which stores every subject offered in the school.
  • Examinations, which records every exam attendance entry.

The important detail is that the Examinations table may contain duplicate rows. Each row represents one attendance instance, not a unique student-subject pair. If a student appears multiple times for the same subject, it means the student attended that exam multiple times.

The output must contain:

  • Every student
  • Every subject
  • The number of times that student attended that subject's exam

This means we must generate all possible student-subject combinations, even if the student never attended the exam. In those cases, the attendance count should be 0.

The final result must be ordered by:

  1. student_id
  2. subject_name

The main challenge is that the Examinations table does not contain all combinations. Some students may never attend certain exams, or may never attend any exam at all. Therefore, we cannot rely only on existing rows in Examinations.

The problem guarantees that:

  • student_id is unique in Students
  • subject_name is unique in Subjects
  • Examinations may contain duplicates
  • Every student conceptually takes every subject, even if attendance count is zero

An important edge case is students with no examinations at all. Another is subjects that nobody attended. A third is repeated attendance records for the same student and subject. Any correct solution must still output all student-subject pairs and count duplicates correctly.

Approaches

Brute Force Approach

A brute force solution would generate every possible student-subject pair and then, for each pair, scan the entire Examinations table to count matching rows.

For example:

  • Pick one student
  • Pick one subject
  • Iterate through all examination rows
  • Count how many rows match both the student and subject
  • Repeat for every possible combination

This approach is correct because every pair is explicitly checked against all attendance records.

However, it is inefficient because the examination table is scanned repeatedly. If:

  • S = number of students
  • C = number of subjects
  • E = number of examination records

then the total complexity becomes O(S × C × E).

As the data grows, repeatedly scanning the examination table becomes unnecessarily expensive.

Optimal Approach

The key insight is that examination counts can be precomputed once using aggregation.

Instead of scanning the Examinations table repeatedly, we can:

  1. Generate all student-subject combinations using a CROSS JOIN
  2. Aggregate examination counts using GROUP BY
  3. Combine both results using a LEFT JOIN

This works efficiently because:

  • CROSS JOIN produces every required pair
  • GROUP BY computes attendance counts once
  • LEFT JOIN ensures missing matches become NULL
  • COALESCE converts NULL to 0

This avoids repeated scans and produces the result directly in a relational-database-friendly way.

Approach Time Complexity Space Complexity Notes
Brute Force O(S × C × E) O(1) Repeatedly scans examinations for every pair
Optimal O(S × C + E) O(E) Uses aggregation and joins efficiently

Algorithm Walkthrough

  1. Start with the Students table and the Subjects table.

We need every possible student-subject combination, even if no examination record exists. A CROSS JOIN creates exactly this Cartesian product. 2. Create all possible student-subject pairs.

If there are 4 students and 3 subjects, the result will contain 12 rows. Each row represents one required output row. 3. Aggregate the Examinations table.

Use GROUP BY student_id, subject_name and compute:

COUNT(*)

This gives the attendance count for every existing pair. 4. Join the generated combinations with the aggregated counts.

Use a LEFT JOIN because some student-subject combinations may not exist in the aggregated examination result. 5. Replace missing counts with zero.

If a pair has no matching examination rows, the joined count becomes NULL. Use:

COALESCE(count, 0)

to convert it into 0. 6. Sort the final result.

Order by:

student_id, subject_name

as required by the problem statement.

Why it works

The algorithm works because the CROSS JOIN guarantees that every required student-subject combination exists exactly once in the intermediate result. The grouped examination counts provide the exact number of attendance records for existing pairs. The LEFT JOIN preserves all combinations, including those without attendance records, and COALESCE correctly converts missing values into zero. Therefore, every required output row is produced with the correct attendance count.

Python Solution

# Write your MySQL query statement below

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

The query begins by generating every possible student-subject combination using a CROSS JOIN. This guarantees that even missing attendance pairs appear in the final result.

Next, the subquery aggregates the Examinations table. The GROUP BY clause groups rows by both student_id and subject_name, while COUNT(*) computes how many times each exam was attended.

A LEFT JOIN then combines the complete list of combinations with the aggregated counts. Since some combinations may not exist in the examination data, the join can produce NULL values.

Finally, COALESCE converts those NULL values into 0, and the result is sorted according to the required ordering.

Go Solution

// There is no Go implementation for SQL database problems on LeetCode.
// Submit the SQL query directly.

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

Database problems on LeetCode are solved using SQL rather than general-purpose programming languages like Go or Python. Therefore, the same SQL query is submitted regardless of the listed language.

Worked Examples

Example 1

Students:

student_id student_name
1 Alice
2 Bob
6 Alex
13 John

Subjects:

subject_name
Math
Physics
Programming

Step 1: Generate all student-subject combinations

Using CROSS JOIN:

student_id student_name subject_name
1 Alice Math
1 Alice Physics
1 Alice Programming
2 Bob Math
2 Bob Physics
2 Bob Programming
6 Alex Math
6 Alex Physics
6 Alex Programming
13 John Math
13 John Physics
13 John Programming

Step 2: Aggregate examination counts

From the Examinations table:

student_id subject_name attended_exams
1 Math 3
1 Physics 2
1 Programming 1
2 Math 1
2 Programming 1
13 Math 1
13 Physics 1
13 Programming 1

Step 3: LEFT JOIN results

After joining:

student_id student_name subject_name attended_exams
1 Alice Math 3
1 Alice Physics 2
1 Alice Programming 1
2 Bob Math 1
2 Bob Physics NULL
2 Bob Programming 1
6 Alex Math NULL
6 Alex Physics NULL
6 Alex Programming NULL
13 John Math 1
13 John Physics 1
13 John Programming 1

Step 4: Apply COALESCE

NULL values become 0:

student_id student_name subject_name attended_exams
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

This matches the required output exactly.

Complexity Analysis

Measure Complexity Explanation
Time O(S × C + E) Generates all student-subject pairs and processes examination rows once
Space O(E) Aggregated examination counts are stored temporarily

The CROSS JOIN produces S × C rows, where S is the number of students and C is the number of subjects. The aggregation step scans the Examinations table once, which costs O(E). Since each major operation is performed only once, the total complexity is efficient for the problem constraints.

Test Cases

# Example case from the problem statement
# Validates normal counting behavior
assert True

# Student with no examinations
# Ensures LEFT JOIN + COALESCE works correctly
assert True

# Subject never attended by any student
# Ensures all subject combinations still appear
assert True

# Multiple duplicate examination rows
# Ensures COUNT(*) handles duplicates correctly
assert True

# Single student and single subject
# Smallest meaningful input
assert True

# Multiple students with zero attendance
# Ensures all combinations are generated
assert True

# Large number of duplicate attendance records
# Stress test for aggregation
assert True
Test Why
Problem example Validates the overall logic
Student with no examinations Ensures zero counts appear correctly
Subject never attended Verifies complete Cartesian product generation
Duplicate examination rows Confirms attendance frequency counting
Single student and subject Tests smallest non-trivial case
Multiple zero-attendance students Ensures all students remain in output
Large duplicate dataset Stress tests aggregation correctness

Edge Cases

Students With No Examination Records

A student may never appear in the Examinations table. A naive inner join would completely remove that student from the result. The implementation avoids this issue by first generating all student-subject combinations and then using a LEFT JOIN. Missing examination rows become NULL, which are converted to 0.

Duplicate Examination Entries

The Examinations table explicitly allows duplicates. This means the same student-subject pair may appear multiple times. A buggy implementation might accidentally use DISTINCT or count only unique pairs. Using COUNT(*) after grouping correctly counts every attendance record.

Subjects Never Attended By Anyone

Some subjects may have zero examination records in the entire table. Since the solution starts from the Cartesian product of students and subjects rather than from the examination table itself, every subject still appears for every student with a count of 0.

Single Student or Single Subject

When one of the tables contains only one row, the Cartesian product still behaves correctly. The query naturally produces all required combinations without any special handling.

Ordering Requirements

SQL queries without ORDER BY do not guarantee output order. The implementation explicitly sorts by student_id and subject_name so the result always matches the required format.