Abstract / Summary

Given a list of teacher-subject pairs, count how many different subjects each teacher has taught.

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 89% (600k+ 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 provided SQL statement extracts, for each teacher, the number of unique subjects they are associated with. It aggregates rows from the Teacher table, groups them by teacher_id, and counts distinct subject_id values within each group. The result set contains two columns: the identifier of the teacher and the count of different subjects taught.

SELECT teacher_id, COUNT(DISTINCT subject_id) AS cnt
  FROM Teacher
 GROUP BY teacher_id

Key Steps

Selecting Columns

The query begins by selecting teacher_id, which serves as the grouping key, and an aggregate expression COUNT(DISTINCT subject_id). The alias cnt gives a readable name to the computed count.

Aggregation

COUNT(DISTINCT subject_id) tallies only unique subject_id values for each teacher. If a teacher appears multiple times with the same subject, those repetitions are ignored, ensuring the count reflects distinct subjects rather than total rows.

Grouping

GROUP BY teacher_id partitions the rows of the Teacher table into subsets, each containing all rows that share the same teacher_id. The aggregate function is then applied to each subset independently, producing one output row per teacher.

Edge Cases Handled

When a teacher has no associated rows in the Teacher table, the query naturally excludes that teacher from the result because there is no row to group. If a teacher appears but all subject_id values are NULL, the COUNT(DISTINCT ...) treats NULL as non-existent, yielding a count of zero for that teacher. Duplicate subject entries for a teacher are collapsed by the DISTINCT qualifier, preventing over-counting. The query also gracefully handles the situation where the Teacher table contains a single row per teacher, returning a count of one for each non-null subject.

Performance Optimizations Observed

The execution plan for this pattern typically benefits from indexes on the grouping and distinct columns. An index on (teacher_id, subject_id) provides a covering path: the database can read the index entries directly, satisfying both the grouping key and the distinct subject values without touching the base table. This reduces I/O and speeds up the hash or sort aggregation used to compute the distinct counts.

If the underlying engine supports index-only aggregation, the COUNT(DISTINCT ...) can be performed directly on the index, avoiding a separate distinct-value sort step. The presence of a composite index also enables the optimizer to use a “group by” shortcut where the rows are already ordered by teacher_id, allowing a streaming aggregation that processes rows sequentially without additional sorting.

When the table is large, the database may choose a hash aggregation strategy, building an in-memory hash table keyed by teacher_id and maintaining a set of distinct subject_id values per key. The hash table size is limited by available memory, and the engine may spill to disk if necessary, but the composite index helps keep the hash table small by providing pre-filtered, ordered input.

Overall, the query leverages standard SQL aggregation mechanisms and can achieve efficient execution when appropriate indexes are present, especially a composite index covering both grouping and distinct columns.

Imagine a big classroom filled with teachers and all the different subjects they teach. This code acts like a helper who looks at a list of names and activities. The helper goes through the whole list and gathers every teacher who shares a name. Since one teacher might teach the same subject to different classes, the helper carefully counts only the unique subjects for each person. Once the counting is done, the helper makes a neat little chart that shows exactly how many different subjects each teacher is in charge of. It is just like organizing a pile of colorful blocks and counting how many different colors you have.

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	muhammadfarhankt	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	SQL
Progiez	C++, Java, Python
muhammadfarhankt	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