Watch 10 video solutions for Number of Unique Subjects Taught by Each Teacher, a easy level problem involving Database. This walkthrough by Learn With Chirag has 6,199 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Teacher
+-------------+------+ | Column Name | Type | +-------------+------+ | teacher_id | int | | subject_id | int | | dept_id | int | +-------------+------+ (subject_id, dept_id) is the primary key (combinations of columns with unique values) of this table. Each row in this table indicates that the teacher with teacher_id teaches the subject subject_id in the department dept_id.
Write a solution to calculate the number of unique subjects each teacher teaches in the university.
Return the result table in any order.
The result format is shown in the following example.
Example 1:
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 | | 2 | 4 | 1 | +------------+------------+---------+ Output: +------------+-----+ | teacher_id | cnt | +------------+-----+ | 1 | 2 | | 2 | 4 | +------------+-----+ Explanation: Teacher 1: - They teach subject 2 in departments 3 and 4. - They teach subject 3 in department 3. Teacher 2: - They teach subject 1 in department 1. - They teach subject 2 in department 1. - They teach subject 3 in department 1. - They teach subject 4 in department 1.
Problem Overview: You are given a table where each row represents a teacher teaching a specific subject. A teacher may appear multiple times if they teach multiple subjects. The task is to compute how many unique subjects each teacher teaches and return the result per teacher.
Approach 1: Using Hash Maps and Sets (O(n) time, O(n) space)
Iterate through every record and group subjects by teacher_id. Use a hash map where the key is the teacher ID and the value is a set of subject IDs. As you process each row, insert the subject_id into the set for that teacher. Sets automatically remove duplicates, which guarantees that each subject is counted once. After processing all rows, compute the size of each set to get the number of unique subjects taught by that teacher.
This approach mirrors how you would solve the problem in application code rather than directly in SQL. It works well when the data is already loaded in memory (for example, inside a backend service or coding interview problem). Since each row is processed once and set insertion is O(1) on average, the overall time complexity is O(n) with O(n) space for storing grouped subjects.
Approach 2: SQL-like Grouping and Aggregation (O(n) time, O(k) space)
Database problems are naturally solved using database aggregation. Group all rows by teacher_id and compute the number of distinct subjects using COUNT(DISTINCT subject_id). The database engine scans the table, groups rows with the same teacher, and removes duplicate subject IDs within each group before counting.
This method leverages optimized database grouping operations and is the most natural solution when working directly with relational data. Internally, databases often use hashing or sorting to perform grouping, but the conceptual complexity remains O(n) time for scanning rows and roughly O(k) space where k is the number of teachers being grouped.
Recommended for interviews: Interviewers typically expect the grouping idea immediately. The hash map + set approach shows you understand how to simulate SQL aggregation using core data structures. In database-focused questions, the GROUP BY teacher_id with COUNT(DISTINCT subject_id) solution is the cleanest and most direct representation of the logic.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Hash Map + Set Grouping | O(n) | O(n) | When processing rows in application code (Python/JS) without direct SQL aggregation |
| SQL GROUP BY with COUNT(DISTINCT) | O(n) | O(k) | Best for relational database queries where grouping and aggregation are supported |
