Number of Unique Subjects Taught by Each Teacher
Ed Tech
Problem Statement
University administrators need to report on faculty workload. Specifically, they want to know how many different subjects each teacher handles, regardless of how many departments those subjects span.
Write an SQL query to report the number of unique subjects each teacher teaches in the university.
Rules:
- A teacher may teach the same subject in multiple departments, but it should only be counted once for that teacher.
- Return the teacher_id and the count of unique subjects (aliased as cnt).
- Results must be sorted by teacher_id in ascending order.
Tests your understanding of
Basic SQL, Aggregation, COUNT DISTINCT and Grouping
Input Tables
teacher
| teacher_id(INTEGER) | subject_id(INTEGER) | dept_id(INTEGER) |
|---|---|---|
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 1 | 3 | 3 |
| 2 | 1 | 1 |
| 2 | 2 | 1 |
| 2 | 3 | 1 |
| 2 | 4 | 1 |
Expected Output
| teacher_id(INTEGER) | cnt(INTEGER) |
|---|---|
| 1 | 2 |
| 2 | 4 |
Tags
EasyBasic SQLAggregationCOUNT DISTINCTGrouping
5-10 min
88%