Solutions to the ctci_student_enrollment_db challenge

ctci_student_enrollment_db_solution.md

0. Draw an entity relationship diagram (or state in words the entity relationships) that accurately describes this database.

student is enrolled in 0 or more courses
teacher teaches 0 or more courses
course has 0 or more students enrolled

there is a many-to-many relationship between students and courses
there is a one-to-many relationship between teachers and courses

1. Implement a query to get a list of all students and how many courses each student is enrolled in.

SELECT
  s.student_id
  , s.student_name
  , COUNT(sc.course_id) AS course_count
FROM students s
  LEFT JOIN student_courses sc
  ON s.student_id = sc.student_id
GROUP BY
  s.student_id
  , s.student_name
;

2. Implement a query that shows the number of full-time and part-time students. A full-time student is enrolled in at least 4 courses. A part-time student is enrolled in at least 1 course, but no more than 3.

WITH enrolled_student_course_counts AS (
  SELECT
    s.student_id
    , s.student_name
    , COUNT(sc.course_id) AS course_count
  FROM students s
    LEFT JOIN student_courses sc
    ON s.student_id = sc.student_id
  GROUP BY
    s.student_id
    , s.student_name
  HAVING COUNT(sc.course_id) > 0
)
, student_enrollement_statuses AS (
  SELECT
    student_id
    , student_name
    , CASE WHEN course_count >= 4 THEN 'full-time'
           WHEN course_count BETWEEN 1 AND 3 THEN 'part-time'
      END AS student_enrollment_status
  FROM enrolled_student_course_counts
)
SELECT
  UPPER(student_enrollment_status) AS student_enrollement_status
  , COUNT(student_enrollment_status) AS student_enrollement_status_count
FROM student_enrollement_statuses
GROUP BY student_enrollment_status
;

3. Write a query that shows which teacher(s) are teaching the most number of courses.

WITH teacher_course_rankings AS (
  SELECT
    t.teacher_id
    , t.teacher_name
    , COUNT(c.course_id) AS teacher_course_count
    , RANK() OVER (ORDER BY COUNT(c.course_id) DESC) AS teacher_course_rank
  FROM teachers t
    LEFT JOIN courses c
    ON t.teacher_id = c.teacher_id
  GROUP BY
    t.teacher_id
    , t.teacher_name
)
SELECT
  teacher_id
  , teacher_name
FROM teacher_course_rankings
WHERE teacher_course_rank = 1
;

4. Write a query that shows which teacher(s) are teaching the least number of courses.

WITH teacher_course_rankings AS (
  SELECT
    t.teacher_id
    , t.teacher_name
    , COUNT(c.course_id) AS teacher_course_count
    , RANK() OVER (ORDER BY COUNT(c.course_id)) AS teacher_course_rank
  FROM teachers t
    LEFT JOIN courses c
    ON t.teacher_id = c.teacher_id
  GROUP BY
    t.teacher_id
    , t.teacher_name
)
SELECT
  teacher_id
  , teacher_name
FROM teacher_course_rankings
WHERE teacher_course_rank = 1
;

5. Write a query that shows which teacher(s) are teaching the most number of students.

WITH teacher_student_rankings AS (
  SELECT
    t.teacher_id
    , t.teacher_name
    , COUNT(DISTINCT sc.student_id) AS teacher_student_count
    , RANK() OVER (ORDER BY COUNT(DISTINCT sc.student_id) DESC) AS teacher_student_rank
  FROM teachers t
    LEFT JOIN courses c
    ON t.teacher_id = c.teacher_id
    LEFT JOIN student_courses sc
    ON c.course_id = sc.course_id
  GROUP BY
    t.teacher_id
    , t.teacher_name
)
SELECT
  teacher_id
  , teacher_name
FROM teacher_student_rankings
WHERE teacher_student_rank = 1
;

6. Write a query that shows which teacher(s) are teaching the least number of students.

WITH teacher_student_rankings AS (
  SELECT
    t.teacher_id
    , t.teacher_name
    , COUNT(DISTINCT sc.student_id) AS teacher_student_count
    , RANK() OVER (ORDER BY COUNT(DISTINCT sc.student_id)) AS teacher_student_rank
  FROM teachers t
    LEFT JOIN courses c
    ON t.teacher_id = c.teacher_id
    LEFT JOIN student_courses sc
    ON c.course_id = sc.course_id
  GROUP BY
    t.teacher_id
    , t.teacher_name
)
SELECT
  teacher_id
  , teacher_name
FROM teacher_student_rankings
WHERE teacher_student_rank = 1
;

7. Write a query that shows what the average number of courses taught by a teacher.

WITH teacher_course_counts AS (
  SELECT
    t.teacher_id
    , t.teacher_name
    , COUNT(c.course_id) AS teacher_course_count
  FROM teachers t
    LEFT JOIN courses c
    ON t.teacher_id = c.teacher_id
  GROUP BY
    t.teacher_id
    , t.teacher_name
)
SELECT
  AVG(teacher_course_count) avg_courses_taught
FROM teacher_course_counts
;

8. Write a query that tells us how many students are not enrolled. Who are these unenrolled students?

WITH student_course_counts AS (
  SELECT
    s.student_id
    , s.student_name
    , COUNT(sc.course_id) AS course_count
  FROM students s
    LEFT JOIN student_courses sc
    ON s.student_id = sc.student_id
  GROUP BY s.student_id
)
, student_enrollement_statuses AS (
  SELECT
    student_id
    , student_name
    , CASE WHEN course_count = 0 THEN 'unenrolled'
           ELSE 'enrolled'
      END AS student_enrollment_status
  FROM student_course_counts
)
SELECT
  UPPER(student_enrollment_status) AS student_enrollement_status
  , COUNT(student_enrollment_status) AS student_enrollement_status_count
FROM student_enrollement_statuses
WHERE student_enrollment_status = 'unenrolled'
GROUP BY student_enrollment_status
;

9. Write a query that lists the courses in order of most popular to least popular.

SELECT
  c.course_id
  , c.course_name
  , COUNT(sc.student_id) AS student_count
FROM courses c
  LEFT JOIN student_courses sc
  ON c.course_id = sc.course_id
GROUP BY
  c.course_id
  , c.course_name
ORDER BY 3 DESC
;