How can I calculate rank based on highest points and lowest time in SQL server

Question

I have searched and searched but have been unable to get this working. I'd really appreciate it if someone could help me out.

I have a table that stores the information for games played (small quiz application.) I need to be able to determine the top 5 ranked players based on having the highest score, followed by the lowest time. (Many players have the max score). I also need to determine the users rank at any given time.

Here is a sample of the games table:

user_id  credits  time_played  alias
-------  -------  -----------  --------------
64       490      180          DaPlaya
93       690      187          Superman
64       336      187          DaPlaya
75       196      192          Flake
93       182      197          Superman
57       844      198          John Smith
75       350      198          Flake
64       858      384          DaPlaya
73       858      400          littleguy
57       858      412          John Smith
101      858      420          FLASH
73       858      423          littleguy
73       858      434          littleguy
65       858      460          Sheena
122      858      540          Sugar Queen
126      858      545          Rachel
176      350      2417         The Fire Light
157      350      2442         Big Q
161      350      2456         Joey Blue

The current query i'm using is this:

select top 5 alias,user_id, 
   rank() over (order by max(credits) desc,min(time_played)) as rank 
from games,users 
where games.user_id=users.id and users.id <> 20 and games.credits > 0 
group by user_id,alias

(As you can see from the query, the alias is actually stored in the users table.)

I thought this query was working, as it seemed to show the correct results, however now that the site is live, and I have thousands of games, I can see that it's wrong.

The above query basically gives me 5 players that have the highest possible score, but it ranks them on their lowest game-time. (so faster games with lower scores).

Please let me know how I can tweak/modify this query or rewrite it entirely.

(Note, I could do this much simpler using php with a loop, but that wouldn't be very efficient.)

I'm happy to provide more information if needed.

Thanks in advance, Aaron.

Answer 1

with user_ranked_games as (
  select user_id,
         credits,
         time_played,
         row_number() over (  partition by user_id 
                              order by credits desc, time_played asc
                           ) as game_rank
    from games
)
select top 5
       rank() over (order by g.credits desc, g.time_played asc) as rank,
       g.user_id,
       u.alias,
       g.credits,
       g.time_played
  from user_ranked_games g
  join users u
    on g.user_id = u.user_id
 where g.game_rank=1
order by rank

I successfully tested the above on SQL Fiddle. I modified the data values so that littleguy has 2 of the top 5 scores. But my query above correctly lists littleguy only once in the ranked top 5 players.

Note - My original sql fiddle had duplicate user rows (I didn't bother defining PKs), which led to different outputs depending on where the join was done. I've since fixed the data and removed the join from the CTE and put it in the outer-most query as I originally intended.

Update in response to comment

A small modification provides a query that can look up the user rank and scores for an individual user:

with user_ranked_games as (
  select user_id,
         credits,
         time_played,
         row_number() over (  partition by user_id 
                              order by credits desc, time_played asc
                           ) as game_rank
    from games
),
ranked_users as (
  select rank() over (order by g.credits desc, g.time_played asc) as rank,
         g.user_id,
         u.alias,
         g.credits,
         g.time_played
    from user_ranked_games g
    join users u
      on g.user_id = u.user_id
   where g.game_rank=1
)
select * from ranked_users where user_id=93

You might want to consider creating a view that can be conveniently used to answer both questions:

create view ranked_users as
with user_ranked_games as (
  select user_id,
         credits,
         time_played,
         row_number() over (  partition by user_id
                              order by credits desc, time_played asc
                           ) as game_rank
    from games
),
select rank() over (order by g.credits desc, g.time_played asc) as rank,
       g.user_id,
       u.alias,
       g.credits,
       g.time_played
  from user_ranked_games g
  join users u
    on g.user_id = u.user_id
 where g.game_rank=1

The first query then becomes:

select top 5 * from ranked_users order by rank

And the 2nd query becomes

select * from ranked_users where user_id=93