Courses per Climbing Instructor

A chain of climbing gyms assigns instructors to teach courses at its branches. The same course can be taught by an instructor at several branches, and that should not make the instructor look more versatile.

For each instructor that appears in the schedule, return instructor_id and course_count: the number of different courses they teach, no matter at how many branches. Rows may be in any order. If the schedule is empty, return no rows.

Tables

class_assignments — One row per instructor, course and branch combination on the schedule.

Example

class_assignments

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Aggregation & Grouping · problem 9 of 70 · SQLite

The idea

Each row is an (instructor, course, branch) triple, so an instructor who teaches one course at three branches has three rows for that course. Counting rows would therefore measure workload across branches, not breadth of skills.

COUNT(DISTINCT course_code) inside a GROUP BY instructor_id counts each course once per instructor, which is exactly the question. Because instructors only appear when they have at least one assignment, the result naturally contains only scheduled instructors, and an empty table gives no rows.

The alternative makes the deduplication explicit: first SELECT DISTINCT instructor_id, course_code to get one row per instructor-course pair, then count rows per instructor. Both are common; the first is shorter, the second is easier to extend when you need several distinct counts that depend on the same deduplicated set.

Solution

SELECT
  instructor_id,
  COUNT(DISTINCT course_code) AS course_count
FROM class_assignments
GROUP BY instructor_id;

Another way

SELECT
  instructor_id,
  COUNT(*) AS course_count
FROM (
  SELECT DISTINCT instructor_id, course_code
  FROM class_assignments
) AS pairs
GROUP BY instructor_id;

Why common answers fail

Counts course-branch rows, so a course taught at three branches counts three times.

SELECT instructor_id, COUNT(*) AS course_count
FROM class_assignments
GROUP BY instructor_id;

Counts distinct branches instead of distinct courses.

SELECT instructor_id, COUNT(DISTINCT branch_id) AS course_count
FROM class_assignments
GROUP BY instructor_id;

What the interviewer is checking

A quick screen for whether you understand the grain of a table and reach for COUNT(DISTINCT) when rows repeat a value.

Mistakes to avoid

• COUNT(*) counting each branch separately.
• Counting the wrong column with DISTINCT.
• Grouping by course_code as well, which splits the instructor into several rows.

Follow-ups to expect

• Only instructors who teach at least 3 different courses (HAVING COUNT(DISTINCT course_code) >= 3).
• List the courses as text too (group_concat(DISTINCT course_code)).
• Count instructors per course instead (swap the grouping and distinct columns).

In real work

Staffing and skills matrices: counting distinct skills, products or regions per person in a table that repeats them across locations.

Practice next