Abstract / Summary

Given lists of students and exams, count how many students did not take any exam.

Note

The full problem description, including helpful examples, can be found on LeetCode.

This problem is ranked as

and has an acceptance rate of about 62% (1+ million submissions).

Typical patterns to solve this problem:

• Database

Every single line of code was written by me, 100% from scratch, between August 2023 and August 2024.
However, two years later I set up this website and used various AI tools to generate most of the walkthrough / explanations below.

See this dedicated page for more details.

Overview

The script builds a complete matrix of every student paired with every subject and then determines how many examinations each student has attended for each subject. The result lists every student-subject combination, even when no examinations exist, and shows a count of attended exams that is zero in those cases. The query is structured with a common table expression (CTE) that materializes the Cartesian product, followed by a left outer join to the examinations table, aggregation, and final ordering.

Construction of the Cartesian Product

The first step creates a CTE named crossed that enumerates all possible student-subject pairs.

WITH crossed AS (
    SELECT st.student_id,
           st.student_name,
           su.subject_name
    FROM   Students st,
           Subjects su
)

By selecting from both Students and Subjects without a join condition, the query generates every combination of rows from the two tables. This guarantees that each student appears once for each subject, establishing the foundation for a complete attendance matrix.

Joining Examinations and Counting Attendance

The next phase left-joins the crossed CTE with the Examinations table on both student and subject identifiers, then aggregates the matches.

SELECT c.student_id,
       c.student_name,
       c.subject_name,
       COUNT(e.subject_name) AS attended_exams
FROM   crossed c
LEFT   JOIN Examinations e
       ON c.student_id   = e.student_id
      AND c.subject_name = e.subject_name
GROUP  BY c.student_id,
          c.subject_name
ORDER  BY c.student_id,
          c.subject_name;

The left outer join preserves every row from crossed. When a matching examination record exists, the join produces a non-null e.subject_name; otherwise the columns from e are null. The COUNT(e.subject_name) expression counts only non-null values, so rows without matching examinations contribute a count of zero.

Edge Cases Handled by the Query

When a student has never taken an exam for a particular subject, the left join yields a row where all Examinations columns are null. Because COUNT ignores nulls, the attended_exams column correctly reports zero. Similarly, if a subject has never been examined for any student, the Cartesian product still creates rows for that subject, and each row will again show a zero count. The query also gracefully handles students who have no examinations at all; they appear once per subject with a zero count. The grouping clause includes only student_id and subject_name, which means that multiple rows for the same student-subject pair (e.g., multiple exam attempts) are collapsed into a single aggregated result.

Performance Optimizations Observed

The use of a CTE isolates the Cartesian product computation, allowing the database engine to materialize it once and reuse it for the subsequent join. This can reduce repeated evaluation of the cross-join logic. The left join is performed on indexed columns (student_id and subject_name), which enables the optimizer to use index lookups rather than full scans of the Examinations table for each pair. Counting only the non-null e.subject_name avoids an extra CASE expression and leverages the built-in null-aware aggregation. Finally, the ORDER BY clause follows the same columns used in the GROUP BY, allowing the engine to produce sorted output without an additional sorting pass if the grouping operation already yields rows in the required order.

Imagine every student needs to take every possible test, even if they have not actually walked into the classroom to take one yet. First, the code creates a very long list that pairs every student with every single subject so that nobody is left out. Think of this like making a giant chart of every person and every class.

After that list is ready, the code looks at the real records of students who actually went to take their exams. It matches those real test records up with the giant chart from before. Because some students might have skipped a test, the code keeps them on the list anyway and marks that they have attended zero exams for that subject. Finally, the computer counts how many times each student showed up for each specific subject and organizes the names and classes into a neat order so everything is easy to read.

Several LLMs ranked my code quality on a scale from 1 (very poor) to 10 (outstanding):

not analyzed yet

I wrote a basic crawler to find other solutions to that problem.
Obviously, I did that after I solved the problem myself.

In general, AlgoMonster and WalkCCC solutions are high-quality and always worth a look.
I haven't checked most of the other solutions, but they might be helpful as well.

And of course, the LeetCode forum is always a good place (or even the best) to look for alternative approaches.

(click on a column header to sort the table)

	author
author	solutions
www	AlgoMonster		C++	Java	Python	TS
	WalkCCC	SQL
	leetcode.ca	SQL
	Progiez		C++	Java	Python
github	suxrobGM	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	SQL
Progiez	C++, Java, Python
suxrobGM	SQL

There are a few extra pages giving you some insights into my setup:

• Tech Stack - my development environment and tools
• AI - how AI helped me create this website (NOT solving the problems)
• Progress - progression over time
• Quality - code quality