Number of Unique Subjects Taught by Each Teacher | LeetCode 2356

This chapter marks the transition into the Sorting and Grouping category of our SQL interview series. We explore LeetCode Problem 2356, an easy-level problem that tests fundamental aggregation concepts, specifically how to combine GROUP BY clauses with the DISTINCT keyword to compute unique counts per entity.

1. Problem Statement and Status Quo

In database management systems, university administration or academic tracking systems often store relational records where a single teacher may teach multiple subjects across various departments. When querying such tables, naive aggregation can lead to duplicated counts if a teacher teaches the same subject multiple times or across overlapping departments.

• Objective: Write an SQL query to calculate the number of unique subjects each teacher teaches in the university.
• Output Requirement: Return a result table containing teacher_id and the count of unique subjects taught, in any order.
• Difficulty Level: Easy (LeetCode 2356).

2. Database Schema and Constraints

The input is modeled using a single table named Teacher.

• Primary Key: The combination of (subject_id, department_id) or unique row constraints indicates how records are structured. Note that a teacher can teach multiple subjects, and a subject can be taught by multiple teachers.
• Key Insight: Because a single teacher might be associated with multiple rows for the same subject (e.g., across different departments or administrative entries), we must ensure we only count unique or distinct subjects per teacher.

3. Core Mental Model and Analogy

Real-Life Analogy: The University Registrar's Logbook

Analogy: Imagine a university registrar's logbook where every time Professor Smith teaches a class in a lecture hall, an entry is written down. Professor Smith might teach Mathematics in Room A and Mathematics again in Room B. If the dean asks, "How many unique subjects does Professor Smith teach?" we cannot simply count every line in the logbook. We must look at the logbook, list Professor Smith's subjects, cross out duplicates, and count only the distinct subject names.

4. Step-by-Step Data Walkthrough

Let us examine a sample input table and trace how the aggregation transforms the data.

Input Table: Teacher

Step-by-Step Breakdown:

1. Group by teacher_id: We partition our rows into groups based on each unique teacher_id. Here, we get two distinct groups: teacher_id = 1 and teacher_id = 2.
2. Evaluate teacher_id = 1:
   • Associated subject_id values: [2, 2, 3]
   • Applying DISTINCT on subjects: [2, 3]
   • Count of unique subjects: 2
3. Evaluate teacher_id = 2:
   • Associated subject_id values: [1, 2, 3, 4]
   • Applying DISTINCT on subjects: [1, 2, 3, 4]
   • Count of unique subjects: 4

Expected Output Table:

5. Naive vs. Better Approach

The Naive Approach (Flawed)

If we simply use COUNT(subject_id) without filtering for uniqueness, SQL will count every single row associated with that teacher, including duplicate subject entries across different departments.

-- Naive Query (Incorrect if duplicate subject-department records exist)
SELECT teacher_id, COUNT(subject_id) AS cnt
FROM Teacher
GROUP BY teacher_id;

• Why it fails: If teacher_id = 1 teaches subject_id = 2 twice (in two different departments), the naive COUNT returns 3 instead of the true unique count of 2.

The Production-Ready Approach

To solve this, we incorporate the DISTINCT keyword inside the aggregate function COUNT(). This instructs the SQL engine to filter out duplicate subject IDs before performing the count within each group.

-- Production-Ready Query
SELECT 
    teacher_id, 
    COUNT(DISTINCT subject_id) AS cnt
FROM 
    Teacher
GROUP BY 
    teacher_id;

6. Complete SQL Query Implementation

Below is the complete, clean SQL query submitted and verified on LeetCode:

-- LeetCode 2356: Number of Unique Subjects Taught by Each Teacher
SELECT 
    teacher_id, 
    COUNT(DISTINCT subject_id) AS cnt
FROM 
    Teacher
GROUP BY 
    teacher_id;

Execution Flow Walkthrough:

7. Real-World Practical Pitfalls & Edge Cases

When writing aggregation queries involving COUNT(DISTINCT ...), keep these production considerations in mind:

• Handling NULL Values: The SQL COUNT() function ignores NULL values. If subject_id contained NULL entries, COUNT(DISTINCT subject_id) would automatically exclude them from the distinct tally.
• Performance at Scale (BIGINT and Indexing): On massive datasets with millions of rows, COUNT(DISTINCT ...) can be computationally expensive because it requires sorting or hash-aggregation to eliminate duplicates.
  • Optimization: Ensure an index exists on the grouping column (teacher_id) or composite indexes covering (teacher_id, subject_id) to speed up grouping and distinct scans.
• Column Aliasing: Always alias your calculated aggregate columns (e.g., AS cnt) to match expected interface schemas or API contracts, preventing downstream parsing errors.

Summary and Key Takeaways

• Grouping Data: Use GROUP BY teacher_id to partition table rows into distinct buckets per teacher.
• Uniqueness in Aggregations: Combine aggregate functions with the DISTINCT keyword (COUNT(DISTINCT column_name)) to count only unique occurrences within each group.
• Avoiding Row Inflation: Guard against inflated metrics caused by multi-department or multi-entry records by always evaluating whether business logic requires distinct counting.
• Result Ordering: Unless an explicit ORDER BY clause is mandated by the prompt, returning rows in any order (as permitted by GROUP BY) is completely acceptable.
• Simplicity of Easy Problems: Even straightforward interview questions test your precision with basic clauses (FROM, GROUP BY, SELECT, and aggregate modifiers).