Interview Questions/SQL/Number of Unique Subjects Taught by Each Teacher

Number of Unique Subjects Taught by Each Teacher

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

Medium

Number of Unique Subjects Taught by Each Teacher

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.

Examples

Example 1

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.

query.sql