MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.30


CREATE TABLE students (
    student_id int,
    department_id int
);

INSERT INTO students (student_id, department_id)
VALUES 
(1, 1),
(2, 1),
(3, 2),
(4, 2),
(5, 3),
(6, 3),
(7, 4),
(8, 4),
(9, 5),
(10, 5);

CREATE TABLE department (
    department_id int,
    department_name varchar(255)
);

INSERT INTO department (department_id, department_name)
VALUES 
(1, 'Informatics'),
(2, 'Biology'),
(3, 'Physics'),
(4, 'Geography'),
(5, 'Chemistry');

CREATE TABLE exam_results (
    student_id int,
    grade int
);

INSERT INTO exam_results (student_id, grade)
VALUES 
(1, 6),
(2, 6),
(3, 4),
(4, 4),
(5, 3),
(6, 3),
(7, 2),
(8, 2),
(9, 6),
(10, 5);

Records: 10  Duplicates: 0  Warnings: 0



Records: 5  Duplicates: 0  Warnings: 0



Records: 10  Duplicates: 0  Warnings: 0


SELECT * FROM students;

SELECT * FROM department;

SELECT * FROM exam_results;


student_id
department_id


1
1

2
1

3
2

4
2

5
3

6
3

7
4

8
4

9
5

10
5




department_id
department_name


1
Informatics

2
Biology

3
Physics

4
Geography

5
Chemistry




student_id
grade


1
6

2
6

3
4

4
4

5
3

6
3

7
2

8
2

9
6

10
5


WITH CTE as
  (SELECT 
  d.department_name as department_name,e_r.grade, count(e_r.grade) as cnt
    , DENSE_RANK() OVER (ORDER BY e_r.grade DESC ,count(e_r.grade) DESC) rk
FROM  exam_results e_r
      INNER JOIN students s ON e_r.student_id = s.student_id
      INNER JOIN department d ON s.department_id = d.department_id
  WHERE (d.department_name,e_r.grade) IN
(select d.department_name as department_name, MAX(e_r.grade) 
      from exam_results e_r
      INNER JOIN students s ON e_r.student_id = s.student_id
      INNER JOIN department d ON s.department_id = d.department_id     
      group by d.department_name)
group by d.department_name, e_r.grade)
SELECT department_name,grade,cnt FROM CTE WHERE rk = 1


department_name
grade
cnt


Informatics
6
2