Number of Unique Subjects Taught by Each Teacher

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You have a Teacher table. Each row records that a teacher teaches a subject in a department. A teacher may teach the same subject in multiple departments. Write a SQL query to report the number of unique subjects each teacher teaches. Return the result in any order. (subject_id, dept_id) is the primary key.

Database Schema

Teacher

Column Name	Type	Description
teacher_id	INT	Teacher identifier
subject_id	INT	Subject identifier
dept_id	INT	Department identifier

Example

Teacher

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

Explanation:

Teacher 1 teaches subject 2 (in depts 3 and 4) and subject 3 - 2 unique subjects. Teacher 2 teaches subjects 1, 2, 3, 4 - 4 unique subjects.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.