Number of Unique Subjects Taught by Each Teacher - Problem

Given a Teacher table that contains information about teachers, subjects, and departments, write a SQL solution to calculate the number of unique subjects each teacher teaches in the university.

The table has the following structure:

  • teacher_id: The ID of the teacher
  • subject_id: The ID of the subject being taught
  • dept_id: The ID of the department

Note that (subject_id, dept_id) forms the primary key, meaning each combination is unique. A teacher can teach the same subject in different departments, but we want to count each unique subject only once per teacher.

Return the result table in any order.

Table Schema

Teacher
Column Name Type Description
teacher_id int The ID of the teacher
subject_id PK int The ID of the subject being taught
dept_id PK int The ID of the department
Primary Key: (subject_id, dept_id)
Note: Each row represents a teacher teaching a specific subject in a specific department. The combination of subject_id and dept_id is unique.

Input & Output

Example 1 — Multiple Teachers with Different Subject Counts
Input 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 2
Output:
teacher_id cnt
1 2
2 4
💡 Note:

Teacher 1 teaches subject 2 in departments 3 and 4, and subject 3 in department 3. Since we count unique subjects, teacher 1 teaches 2 unique subjects (subjects 2 and 3). Teacher 2 teaches subjects 1, 2, 3, and 4, so they teach 4 unique subjects.
Example 2 — Single Teacher Multiple Departments
Input Table:
teacher_id subject_id dept_id
1 1 1
1 1 2
1 1 3
Output:
teacher_id cnt
1 1
💡 Note:

Teacher 1 teaches the same subject (subject 1) in three different departments (1, 2, and 3). Since we only count unique subjects, the result is 1 unique subject.
Example 3 — Multiple Teachers Single Subject Each
Input Table:
teacher_id subject_id dept_id
1 1 1
2 2 2
3 3 3
Output:
teacher_id cnt
1 1
2 1
3 1
💡 Note:

Each teacher teaches exactly one subject in one department, so each teacher has a count of 1 unique subject.

Constraints

  • 1 ≤ teacher_id ≤ 10^5
  • 1 ≤ subject_id ≤ 10^5
  • 1 ≤ dept_id ≤ 10^5
  • (subject_id, dept_id) is the primary key of this table

Visualization

Tap to expand
Unique Subjects per Teacher 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 Primary Key: (subject_id, dept_id) Teacher 1: subjects 2,3 in multiple depts Teacher 2: subjects 1,2,3 in dept 1 ALGORITHM STEPS 1 GROUP BY Group rows by teacher_id 2 COUNT DISTINCT Count unique subject_id 3 IGNORE DEPT Same subject in diff depts counts as 1 4 OUTPUT Return teacher + count SELECT teacher_id, COUNT(DISTINCT subject_id) AS cnt FROM Teacher GROUP BY teacher_id FINAL RESULT teacher_id cnt 1 2 2 3 Teacher 1 teaches 2 unique subjects (subject 2 and 3) Teacher 2 teaches 3 unique subjects (subject 1, 2, and 3) OK Key Insight: COUNT(DISTINCT subject_id) eliminates duplicates when the same subject is taught in multiple departments. GROUP BY teacher_id ensures we get one row per teacher with their unique subject count. TutorialsPoint - Number of Unique Subjects Taught by Each Teacher | Optimal Solution

Related Problems

Asked in
Amazon 12 Google 8 Microsoft 6
32.9K Views
Medium Frequency
~8 min Avg. Time
890 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