Number of Unique Subjects Taught by Each Teacher

Number of Unique Subjects Taught by Each Teacher

What is this problem about?

The Number of Unique Subjects Taught by Each Teacher SQL problem asks you to count how many distinct subjects each teacher teaches. Each row in the Teacher table has (teacher_id, subject_id, dept_id), and a teacher may teach the same subject in multiple departments. Count DISTINCT subjects per teacher. This is a straightforward GROUP BY with COUNT DISTINCT.

Why is this asked in interviews?

Microsoft, Capgemini, Amazon, Google, and Bloomberg ask this easy SQL problem to verify that candidates know to use COUNT(DISTINCT column) rather than COUNT(*) when rows can be duplicated. The database interview pattern is demonstrated at its simplest with a key DISTINCT nuance.

Algorithmic pattern used

GROUP BY + COUNT DISTINCT.

SELECT teacher_id, COUNT(DISTINCT subject_id) AS cnt
FROM Teacher
GROUP BY teacher_id

Example explanation

Teacher table: (1, Math, A), (1, Math, B), (1, Physics, A), (2, Biology, A).

• Teacher 1: Math appears in depts A and B, Physics in A. Distinct subjects = 2.
• Teacher 2: Biology in A. Distinct subjects = 1. Result: (1, 2), (2, 1).

Common mistakes candidates make

• Using COUNT(*) instead of COUNT(DISTINCT subject_id) (counts duplicate department rows).
• Grouping by both teacher_id and subject_id (over-aggregates).
• Using a subquery when a simple GROUP BY with COUNT DISTINCT suffices.
• Forgetting that the same subject in different departments should count as one.

Interview preparation tip

COUNT(DISTINCT column) vs COUNT(*) is one of the most common SQL mistakes. Always ask: "can the same value appear multiple times in this column?" If yes, use DISTINCT. This pattern appears in: "count distinct customers," "count unique products ordered," "count unique skills per employee." Memorize the template: SELECT key, COUNT(DISTINCT value) AS cnt FROM table GROUP BY key. It solves a large fraction of easy-to-medium SQL interview questions.