Number of Unique Subjects Taught by Each Teacher

Number of Unique Subjects Taught by Each Teacher - Problem

Database Easy

Given a Teacher table that tracks which subjects each teacher teaches in different departments, your task is to find how many unique subjects each teacher teaches across the entire university.

The Teacher table has the following structure:

Column Name	Type
teacher_id	int
subject_id	int
dept_id	int

Key points:

The combination (subject_id, dept_id) is the primary key
A teacher can teach the same subject in multiple departments
We need to count each subject only once per teacher, regardless of how many departments they teach it in

Goal: Write a SQL query to calculate the number of unique subjects each teacher teaches and return the results as (teacher_id, cnt) where cnt is the count of unique subjects.

Input & Output

example_1.sql — Basic Case

$ Input: Teacher table: +------------+------------+---------+ | teacher_id | subject_id | dept_id | +------------+------------+---------+ | 1 | 2 | 3 | | 1 | 2 | 4 | | 1 | 3 | 3 | | 2 | 1 | 1 | | 2 | 2 | 1 | | 2 | 3 | 1 | | 2 | 4 | 1 | +------------+------------+---------+

› Output: +------------+-----+ | teacher_id | cnt | +------------+-----+ | 1 | 2 | | 2 | 4 | +------------+-----+

💡 Note: Teacher 1: Teaches subject 2 in departments 3 and 4 (counts as 1 unique subject), and teaches subject 3 in department 3. Total unique subjects = 2. Teacher 2: Teaches 4 different subjects (1, 2, 3, 4) all in department 1. Total unique subjects = 4.

example_2.sql — Single Teacher

$ Input: Teacher table: +------------+------------+---------+ | teacher_id | subject_id | dept_id | +------------+------------+---------+ | 5 | 10 | 1 | | 5 | 10 | 2 | | 5 | 10 | 3 | +------------+------------+---------+

› Output: +------------+-----+ | teacher_id | cnt | +------------+-----+ | 5 | 1 | +------------+-----+

💡 Note: Teacher 5 teaches the same subject (subject 10) in three different departments (1, 2, 3). Since we count unique subjects per teacher, this counts as only 1 unique subject.

example_3.sql — No Duplicates

$ Input: Teacher table: +------------+------------+---------+ | teacher_id | subject_id | dept_id | +------------+------------+---------+ | 3 | 1 | 1 | | 3 | 2 | 2 | | 3 | 3 | 3 | | 4 | 4 | 1 | +------------+------------+---------+

› Output: +------------+-----+ | teacher_id | cnt | +------------+-----+ | 3 | 3 | | 4 | 1 | +------------+-----+

💡 Note: Teacher 3 teaches 3 different subjects (1, 2, 3) in different departments with no overlaps. Teacher 4 teaches only 1 subject (4). All subjects are already unique per teacher.

Constraints

1 ≤ teacher_id ≤ 10⁵
1 ≤ subject_id ≤ 10⁵
1 ≤ dept_id ≤ 10⁵
The combination (subject_id, dept_id) is unique (primary key constraint)
Each teacher teaches at least one subject

Visualization

Tap to expand

Understanding the Visualization

Collect Teaching Records

Gather all teaching assignments showing which professor teaches what subject in which department

Group by Professor

Organize all records by teacher_id to analyze each professor's portfolio separately

Identify Unique Subjects

For each professor, identify their unique subjects using DISTINCT, ignoring department duplicates

Count Subject Diversity

Count how many different subjects each professor can teach (their teaching versatility)

Generate Portfolio Report

Output each professor's ID and their unique subject count as the final diversity report

Key Takeaway

🎯 Key Insight: This problem measures teaching versatility (how many different subjects a professor can teach) rather than teaching workload (total classes taught). The DISTINCT keyword in SQL automatically handles the complexity of identifying unique subjects across multiple departments.

Asked in

f Meta 15 G Google 12 a Amazon 8 ⊞ Microsoft 6

The optimal solution uses SQL's GROUP BY with COUNT DISTINCT to efficiently count unique subjects per teacher. The query SELECT teacher_id, COUNT(DISTINCT subject_id) AS cnt FROM Teacher GROUP BY teacher_id leverages the database engine's built-in aggregation optimizations for O(n) time complexity and minimal space usage.

Common Approaches

Approach	Time	Space	Notes
✓ Manual Grouping (Conceptual Brute Force)	O(n log n)	O(n)	Conceptually process each teacher's records individually to count unique subjects
SQL GROUP BY with COUNT DISTINCT (Optimal)	O(n)	O(k)	Use SQL's built-in aggregation to group by teacher and count distinct subjects in one query

Manual Grouping (Conceptual Brute Force) — Algorithm Steps

Get all records from the Teacher table
For each teacher, collect all their subject_ids
Remove duplicate subjects for each teacher
Count the remaining unique subjects
Return teacher_id and count pairs

Visualization

Tap to expand

Step-by-Step Walkthrough

Collect Records

Gather all teaching records for each teacher

Group by Teacher

Organize records by teacher_id

Extract Subjects

List all subject_ids for each teacher

Remove Duplicates

Eliminate duplicate subjects using set operations

Count Unique

Count the size of each teacher's unique subject set

Code -

solution.c — C

// Conceptual brute force approach in C
#include <stdio.h>
#include <stdlib.h>

#define MAX_TEACHERS 1000
#define MAX_SUBJECTS 1000

int teacherSubjects[MAX_TEACHERS][MAX_SUBJECTS];
int teacherExists[MAX_TEACHERS];

int countSubjectsBruteForce(int teacherRecords[][3], int numRecords, int result[][2]) {
    // Initialize arrays
    for (int i = 0; i < MAX_TEACHERS; i++) {
        teacherExists[i] = 0;
        for (int j = 0; j < MAX_SUBJECTS; j++) {
            teacherSubjects[i][j] = 0;
        }
    }
    
    // Collect all subjects for each teacher
    for (int i = 0; i < numRecords; i++) {
        int teacherId = teacherRecords[i][0];
        int subjectId = teacherRecords[i][1];
        
        teacherExists[teacherId] = 1;
        teacherSubjects[teacherId][subjectId] = 1;
    }
    
    // Count unique subjects for each teacher
    int resultCount = 0;
    for (int teacherId = 0; teacherId < MAX_TEACHERS; teacherId++) {
        if (teacherExists[teacherId]) {
            int subjectCount = 0;
            for (int subjectId = 0; subjectId < MAX_SUBJECTS; subjectId++) {
                if (teacherSubjects[teacherId][subjectId]) {
                    subjectCount++;
                }
            }
            result[resultCount][0] = teacherId;
            result[resultCount][1] = subjectCount;
            resultCount++;
        }
    }
    
    return resultCount;
}

int main() {
    int teacherData[][3] = {
        {1, 2, 3}, {1, 2, 4}, {1, 3, 3},
        {2, 1, 1}, {2, 2, 1}, {2, 3, 1}, {2, 4, 1}
    };
    int result[MAX_TEACHERS][2];
    
    int count = countSubjectsBruteForce(teacherData, 7, result);
    
    for (int i = 0; i < count; i++) {
        printf("Teacher %d: %d subjects\n", result[i][0], result[i][1]);
    }
    
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Need to sort/group records by teacher, then process duplicates for each teacher

⚡ Linearithmic

Space Complexity

O(n)

Need to store intermediate results for grouping and duplicate removal

⚡ Linearithmic Space

28.4K Views

Medium Frequency

~8 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Manual Grouping (Conceptual Brute Force) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler