CREATE TABLE teachers(teacher_id, first_name,  last_name) AS
   SELECT 101, 'Keith', 'Stein'  FROM dual UNION ALL
  SELECT 102,  'Roger',  'Wood' FROM dual UNION ALL
  SELECT 103,  'Douglas',  'Kern'   FROM dual UNION ALL
  SELECT 104, 'Paul',  'Weber'    FROM dual UNION ALL
  SELECT 105,  'Jeffrey',  'Lebowitz'    FROM dual UNION  ALL
  SELECT 106,  'Gabby',  'Orr'    FROM dual;


CREATE TABLE students(student_id, first_name,  last_name) AS
   SELECT 1, 'Faith', 'Aaron'  FROM dual UNION ALL
  SELECT 2,  'Lisa',  'Saladino' FROM dual UNION ALL
  SELECT 3,  'Leslee',  'Altman'   FROM dual UNION ALL
  SELECT 4, 'Patty',  'Kern'    FROM dual UNION ALL
  SELECT 5,  'Beth',  'Cooper'    FROM dual UNION  ALL
  SELECT 95,  'Zak',  'Despart'    FROM dual UNION  ALL
  SELECT 96,  'Owen',  'Balbert'    FROM dual UNION  ALL
   SELECT 97,  'Jack',  'Aprile'    FROM dual UNION  ALL
  SELECT 98,  'Nicole',  'Kramer'    FROM dual UNION  ALL
   SELECT 99,  'Jill',  'Coralnick'    FROM dual;


CREATE TABLE courses(course_id, course_name, teacher_id, semester) AS
SELECT 1, 'Geometry', 101, '2022-2' FROM DUAL UNION ALL
SELECT 2, 'Trigonometry', 102, '2022-2' FROM DUAL UNION ALL
SELECT 3, 'Calculus', 103, '2022-2' FROM DUAL UNION ALL
SELECT 4, 'Chemistry', 104, '2022-2' FROM DUAL UNION ALL 
SELECT 5, 'Biology', 105, '2022-2' FROM DUAL UNION ALL 
SELECT 6, 'Physcology', 106, '2022-2' FROM DUAL; 

CREATE TABLE student_courses (student_id,course_id) AS
SELECT 1, 1 FROM dual UNION ALL
SELECT 2, 1 FROM dual UNION ALL
SELECT 3, 1 FROM dual UNION ALL
SELECT 4, 1 FROM dual UNION ALL
SELECT 5, 1 FROM dual UNION ALL
SELECT 1, 2 FROM dual UNION ALL
SELECT 2, 2 FROM dual UNION ALL
SELECT 3, 2 FROM dual UNION ALL
SELECT 4, 2 FROM dual UNION ALL
SELECT 5, 2 FROM dual UNION ALL
SELECT 1, 3 FROM dual UNION ALL
SELECT 2, 3 FROM dual UNION ALL
SELECT 3, 3 FROM dual UNION ALL
SELECT 4, 3 FROM dual UNION ALL
SELECT 5, 3 FROM dual UNION ALL
SELECT 97, 1 FROM dual UNION ALL 
SELECT 97, 3 FROM dual UNION ALL 
SELECT 97, 5 FROM dual UNION ALL
SELECT 97, 6 FROM dual UNION ALL
SELECT 98, 3 FROM dual UNION ALL 
SELECT 98, 4 FROM dual UNION ALL
SELECT 98, 5 FROM dual UNION ALL
SELECT 99, 2 FROM dual UNION ALL 
SELECT 99, 4 FROM dual UNION ALL
SELECT 99, 5 FROM dual UNION ALL
SELECT 99, 6 FROM dual;


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:

TEST> WITH teacher_student_rankings AS (
  2    SELECT c.teacher_id,
  3           RANK() OVER (ORDER BY COUNT(DISTINCT sc.student_id) DESC)
  4             AS teacher_student_rank
  5    FROM courses c JOIN student_courses sc
  6           ON c.course_id = sc.course_id
  7    GROUP BY c.teacher_id
  8  )
  9  SELECT t.teacher_id, initcap(t.first_name)||' '||upper(t.last_name) teacher_name
 10  FROM teachers t JOIN teacher_student_rankings r
 11         ON r.teacher_id = t.teacher_id
 12  WHERE teacher_student_rank = 1
 13  ORDER BY t.teacher_id
 14  /
TEACHER_ID TEACHER_NAME
---------- ----------------
       103 Douglas KERN

TEST> WITH teacher_student_rankings AS (
  2    SELECT c.teacher_id,
  3           RANK() OVER (ORDER BY COUNT(DISTINCT sc.student_id) DESC)
  4             AS teacher_student_rank
  5  -----------------
  6           , COUNT(DISTINCT sc.student_id) AS teacher_student_count
  7  -----------------
  8    FROM courses c JOIN student_courses sc
  9           ON c.course_id = sc.course_id
 10    GROUP BY c.teacher_id
 11  )
 12  SELECT t.teacher_id, initcap(t.first_name)||' '||upper(t.last_name) teacher_name
 13  -----------------
 14         , r.teacher_student_count
 15  -----------------
 16  FROM teachers t JOIN teacher_student_rankings r
 17         ON r.teacher_id = t.teacher_id
 18  WHERE teacher_student_rank = 1
 19  ORDER BY t.teacher_id
 20  /
TEACHER_ID TEACHER_NAME     TEACHER_STUDENT_COUNT
---------- ---------------- ---------------------
       103 Douglas KERN                         7