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
Count Unique Subjects Per TeacherInput: Teacher Tableteacher_idsubject_iddept_id123124133211221GROUP BYCOUNT DISTINCTOutput Resultteacher_idcnt1222Teacher 1: subjects 2,3 → 2 uniqueTeacher 2: subjects 1,2 → 2 unique
Understanding the Visualization
1
Input
Teacher table with teacher_id, subject_id, dept_id
2
Group & Count
GROUP BY teacher_id and COUNT DISTINCT subjects
3
Output
Result with teacher_id and unique subject count
Key Takeaway
🎯 Key Insight: Use COUNT(DISTINCT) when you need to count unique values within groups, perfect for eliminating duplicates in aggregations

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