Query improperly sets rank on user, depending on his total points

Question

I'm trying to get all total points per user, sort them in descending order and print their ranks.My query is working almost fine, but it does not set user's rank properly.

SET @rank := 0;
SELECT (@rank := @rank + 1) AS rank, u.username, SUM(s.score) AS totalScore
FROM solution AS s
INNER JOIN users u ON u.id = s.author_id
GROUP BY u.username
ORDER BY totalScore DESC

User schema:

|---------------------|------------------|
|         id          |     username     |
|---------------------|------------------|
|          1          |       test1      |
|---------------------|------------------|
|          2          |       test2      |
|---------------------|------------------|
|          3          |       test3      |
|---------------------|------------------|
|          8          |       test4      |
|---------------------|------------------|

Solution schema:

|---------------------|------------------|-----------------|
|         id          |     author_id    |      score      |
|---------------------|------------------|-----------------|
|          1          |       1          |       55        |
|---------------------|------------------|-----------------|
|          2          |       2          |       5         |
|---------------------|------------------|-----------------|
|          3          |       3          |       22        |
|---------------------|------------------|-----------------|
|          4          |       8          |       43        |
|---------------------|------------------|-----------------|
|          5          |       8          |       43        |
|---------------------|------------------|-----------------|

The result is:

|---------------------|------------------|-----------------|
|         rank        |     username     |    totalScore   |
|---------------------|------------------|-----------------|
|          4          |       test4      |       86        |
|---------------------|------------------|-----------------|
|          1          |       test1      |       55        |
|---------------------|------------------|-----------------|
|          3          |       test3      |       22        |
|---------------------|------------------|-----------------|
|          2          |       test2      |       5         |
|---------------------|------------------|-----------------|

Why does that happen?

The expected result should be:

|---------------------|------------------|-----------------|
|         rank        |     username     |    totalScore   |
|---------------------|------------------|-----------------|
|          1          |       test4      |       86        |
|---------------------|------------------|-----------------|
|          2          |       test1      |       55        |
|---------------------|------------------|-----------------|
|          3          |       test3      |       22        |
|---------------------|------------------|-----------------|
|          4          |       test2      |       5         |
|---------------------|------------------|-----------------|

Answer 1

As i said in the comment. You have first sum up the score to rank it.

SELECT 
    (@rank:=@rank + 1) rank, username, totalscore
FROM
    (SELECT 
        u.username, SUM(s.score) AS totalScore
    FROM
        solution AS s
    INNER JOIN users u ON u.id = s.author_id
    GROUP BY u.username
    ORDER BY totalScore DESC) t1,
    (SELECT @rank:=0) r1

See example the second query is mine

Schema (MySQL v5.7)

CREATE TABLE users (
  `id` INTEGER,
  `username` VARCHAR(5)
);

INSERT INTO users
  (`id`, `username`)
VALUES
  ('1', 'test1'),
  ('2', 'test2'),
  ('3', 'test3'),
  ('8', 'test4');

CREATE TABLE solution (
  `id` INTEGER,
  `author_id` INTEGER,
  `score` INTEGER
);

INSERT INTO solution
  (`id`, `author_id`, `score`)
VALUES
  ('1', '1', '55'),
  ('2', '2', '5'),
  ('3', '3', '22'),
  ('4', '8', '43'),
  ('5', '8', '43');

---

Query #1

SET @rank := 0;

There are no results to be displayed.

---

Query #2

SELECT (@rank := @rank + 1) AS rank, u.username, SUM(s.score) AS totalScore
FROM solution AS s
INNER JOIN users u ON u.id = s.author_id
GROUP BY u.username
ORDER BY totalScore DESC;

| rank | totalScore | username |
| ---- | ---------- | -------- |
| 4    | 86         | test4    |
| 1    | 55         | test1    |
| 3    | 22         | test3    |
| 2    | 5          | test2    |

---

Query #3

SELECT
(@rank := @rank +1) rank
,username
,totalscore
FROM
(SELECT
u.username,
SUM(s.score) AS totalScore
FROM solution AS s
INNER JOIN users u ON u.id = s.author_id
GROUP BY u.username
ORDER BY totalScore DESC) t1,(SELECT @rank := 0) r1;

| rank | username | totalscore |
| ---- | -------- | ---------- |
| 1    | test4    | 86         |
| 2    | test1    | 55         |
| 3    | test3    | 22         |
| 4    | test2    | 5          |

---

View on DB Fiddle

Answer 2

This will work from MySql:

Select username, totalScore, Rank() over (order by totalScore desc) as Rank 
from (
SELECT  u.username, SUM(s.score) AS totalScore
FROM solution AS s
INNER JOIN users u ON u.id = s.author_id
GROUP BY u.username
ORDER BY totalScore DESC
)t