Get original RANK() value based on row create date

user_391715 0
Viewed 46

Using MariaDB and trying to see if I can get pull original rankings for each row of a table based on the create date.

For example, imagine a scores table that has different scores for different users and categories (lower score is better in this case)

id leaderboardId userId score submittedAt ↓ rankAtSubmit
9 15 555 50.5 2022-01-20 01:00:00 2
8 15 999 58.0 2022-01-19 01:00:00 3
7 15 999 59.1 2022-01-15 01:00:00 3
6 15 123 49.0 2022-01-12 01:00:00 1
5 15 222 51.0 2022-01-10 01:00:00 1
4 14 222 87.0 2022-01-09 01:00:00 1
5 15 555 51.0 2022-01-04 01:00:00 1

The "rankAtSubmit" column is what I'm trying to generate here if possible.

I want to take the best/smallest score of each user+leaderboard and determine what the rank of that score was when it was submitted.

My attempt at this failed because in MySQL you cannot reference outer level columns more than 1 level deep in a subquery resulting in an error trying to reference t.submittedAt in the following query:

SELECT *, (
        SELECT ranking FROM (
            SELECT id, RANK() OVER (PARTITION BY leaderboardId ORDER BY score ASC) ranking
            FROM scores x
            WHERE x.submittedAt <= t.submittedAt
            GROUP BY userId, leaderboardId
        ) ranks
        WHERE ranks.id = t.id
    ) rankAtSubmit
FROM scores t
mariadb mysql sql
1 Answers

What I understand from your question is you want to know the minimum and maximum rank of each user. Here is the code

SELECT  userId, leaderboardId, score, min(rankAtSubmit),max(rankAtSubmit)
FROM scores 
group BY userId,
     leaderboardId,
     scorescode here
user_53938580
Related