Number Of Unique Subjects Taught By Each Teacher - Leetcode Solution

Problem statement:

The problem states that we are given a table of courses where each row is a record of a course’s information (course_id, teacher_id, subject). We need to write a SQL query to find out how many unique subjects each teacher is teaching. The result should have two columns: teacher_id and subject_count, where the subject_count is the number of unique subjects taught by the teacher.

Solution:

To solve this problem in SQL, we need to use GROUP BY and COUNT functions in our query. The GROUP BY keyword is used to group rows that have the same teacher_id and subject together so that we can count them easily. The COUNT function is used to count how many unique subjects each teacher is teaching.

Here is the SQL query:

SELECT teacher_id, COUNT(DISTINCT subject) AS subject_count
FROM courses
GROUP BY teacher_id;

Explanation:

In this query, we are selecting two columns, teacher_id and subject_count. The COUNT function is used to count how many unique subjects each teacher is teaching. We use the DISTINCT keyword to count only unique subjects and ignore the duplicates.

The FROM keyword specifies the table name where we need to perform the operation. Here, we need to perform the operation on the courses table.

The GROUP BY keyword groups the data based on the teacher_id column. Hence, the query will group all the rows that have the same teacher_id together.

After grouping the data, the COUNT function will count the number of distinct subjects that teacher is teaching. Finally, the AS keyword is used to rename the column as 'subject_count'.

Conclusion:

In this article, we have discussed the problem statement and provided a detailed solution to find out the number of unique subjects taught by each teacher using SQL. The solution uses GROUP BY and COUNT functions to group the data based on teacher_id and count the number of distinct subjects taught by each teacher. The query is simple and easy to understand.

1