What would be a good request to find my previous rating in this table?

Question

I am currently using this query to find a player’s rank:

select
    coalesce(
        (
            select count(1)
            from scores b
            where
                b.top > a.top OR
                (
                    b.top = a.top AND
                    b.time < a.time
                )
        ), 0
    ) + 1 Rank
from
    Scores a
where
    user = ?

I have a table with tables:

id           int
user         varchar(100)
time         int (timestamp)
top          int

And a recent table like this:

id           int
user         varchar(100)
time         int (timestamp)
score        int
istopscore   int (boolean 1/0)

The database is already filled with data, so I can’t just change the structure of the database. The last table contains over 200,000 rows, so sorting takes a lot of time. I am trying to find a way to do this as quickly as possible.

How do I find a player’s previous rank? Here is what I tried:

select
    coalesce(
        (
            select count(1)
            from recent b
            where
                b.istopscore = 1 AND
                (
                    (
                        b.score > a.top AND
                        b.time <= a.time
                    ) OR
                    (
                        b.score = a.top AND
                        b.time < a.time
                    )
                )
            ), 0) + 1 Rank
from scores a
where user = ?

The problem with this query is that if the user scored several new top marks, he counts all of them, so he does not give the correct result.

Any help would be greatly appreciated.

Answer 1

, . , count(distinct username), this:

select
    coalesce(
        (
            select count(distinct username)
            from recent b
            where
                b.istopscore = 1 AND
                (
                    (
                        b.score > a.top AND
                        b.time <= a.time
                    ) OR
                    (
                        b.score = a.top AND
                        b.time < a.time
                    )
                )
            ), 0) + 1 Rank
from scores a
where username = 'Echo'