We encountered a case when DENSE_RANK() window function does not work correctly.

CREATE TABLE student(course VARCHAR(10), mark int, name varchar(10), score int);

INSERT INTO student VALUES 
  ('Maths', 60, 'Thulile', 1515),
  ('Maths', 60, 'Pritha', 2000),
  ('Maths', 70, 'Voitto', 2000),
  ('Maths', 55, 'Chun', 1600),
  ('Biology', 60, 'Bilal', 3000),
  ('Biology', 70, 'Roger', 3000),
  (NULL, 80, 'Johnson', 2000);

case 1 - Correct results:
SELECT course,
DENSE_RANK() OVER (PARTITION BY course ORDER BY SUM(mark) DESC) AS dense_rank, mark, name, score
FROM student GROUP BY course, score;
+---------+------------+------+---------+-------+
| course  | dense_rank | mark | name    | score |
+---------+------------+------+---------+-------+
| NULL    |         1 |   80 | Johnson |  2000 |
| Biology |          1 |   60 | Bilal        |  3000 |
| Maths   |          2 |   60 | Thulile    |  1515 |
| Maths   |          3 |   55 | Chun       |  1600 |
| Maths   |          1 |   60 | Pritha     |  2000 |
+---------+------------+------+---------+-------+
5 rows in set (0.001 sec)

case 2 - Wrong results:
SELECT course,
DENSE_RANK() OVER (PARTITION BY course ORDER BY (SUM(mark) * 2) DESC) AS dense_rank, mark, name, score
FROM student GROUP BY course, score;
+---------+------------+------+---------+-------+
| course  | dense_rank | mark | name    | score |
+---------+------------+------+---------+-------+
| NULL    |         1 |   80 | Johnson |  2000 |
| Biology |          1 |   60 | Bilal        |  3000 |
| Maths   |          1 |   60 | Thulile   |  1515 |
| Maths   |          1 |   55 | Chun      |  1600 |
| Maths   |          1 |   60 | Pritha    |  2000 |
+---------+------------+------+---------+-------+
5 rows in set (0.000 sec)