MySQL: How to get leaderboard position, for each event in a series

Question

I have the following data structure (simplified):

Users:
    ID
    Name

Events:
    ID
    Date

Results:
    ID
    User ID (foreign key)
    Event ID (foreign key)
    Points: (int)

I would like to know (ideally the most efficient way):

• How to get a user's position in a 'league' compared to other users. And - - If possible using one query (or sub queries), how to break this down by event, e.g. the user's position after the 1st event, 2nd event, 3rd event etc.

I can get the leaderboard with:

select users.name, SUM(results.points) as points
from results
inner join users on results.user_id = users.id
group by users.id
order by points DESC

However, I'd like to know a user's position without having to return the entire table if possible.

Edit: I have supplied some sample data here.

Ideal output:

| User ID | Rank  |
| 3       | 1     |
| 1       | 2     |
| 2       | 3     |

and something similar to (not exactly like this, it's flexible, just something that shows the user's rank from each event)

| User ID | After Event | Rank  |
| 1       | 1           | 1     |
| 1       | 2           | 1     |
| 1       | 3           | 2     |
| 2       | 1           | 2     |
| 2       | 2           | 2     |
| 2       | 3           | 1     |
| 3       | 1           | 3     |
| 3       | 2           | 3     |
| 3       | 3           | 3     |

Answer 1

MySQL 8.0+ supports window functions so the use of dense_rank() comes in handy.

---

MySQL under 8.0 solution

Since your version is 5.7 you could imitate this like below:

select 
  t.id,
  CASE WHEN @prevRank = points THEN @currRank
       WHEN @prevRank := points THEN @currRank := @currRank + 1
   END AS rank
from (
  select users.id, SUM(results.points) as points
  from results
  inner join users on results.user_id = users.id
  group by users.id
  order by points DESC
  ) t
  cross join (SELECT @currRank := 0, @prevRank := NULL) r

If you need data for particular user then add a WHERE condition to filter out everyone else in an outer query:

select *
from (
<< above query here >>
) t
where id = ? -- your id here

---

MySQL 8.0+ solution

rank is a reserved keyword so backticks are required when naming a column. We're using dense_rank window function which will assign ranks based od descending sorting of points acquired:

select id, dense_rank() over (order by points desc) as `rank`
from (
  select users.id, SUM(results.points) as points
  from results
  inner join users on results.user_id = users.id
  group by users.id
  ) t
order by `rank`