How to compute multiple ranks of a player with one request?

user_13587388 0
Viewed 44

I'm using PostgreSQL and I would like to get a list of all the player's rank from one table.

For example, I have this table "Scores" : (the combination user_id - battlefield_id is unique in this table)

+----------+----------------+-------+
| user_id  | battlefield_id | score |
+==========+================+=======+
| Legolas  | Helm's Deep    | 42    |
+----------+----------------+-------+
| Legolas  | Pelennor       | 100   |
+----------+----------------+-------+
| Gimli    | Helm's Deep    | 43    |
+----------+----------------+-------+
| Gimli    | Pelennor       | 120   |
+----------+----------------+-------+
| Aragorn  | Helm's Deep    | 50    |
+----------+----------------+-------+
| Aragorn  | Pelennor       | 10    |
+----------+----------------+-------+

And I would like a request to compute all the ranks of Aragorn on any of the battlefields and return this kind of result : 

+------+----------------+
| rank | battlefield_id |
+======+================+
| 1    | Helm's Deep    |
+------+----------------+
| 3    | Pelennor       |
+------+----------------+

I currently have this request who works well but I have to specify both the user and the battlefield :

SELECT count(*)+1 AS rank FROM scores WHERE score < 
(
    SELECT score FROM scores WHERE user_id = 'Aragorn' AND battlefield_id = "Pelennor"
)
AND battlefield_id = "Pelennor"

But I have no idea how to adapt that to get the rank on any battlefield. I don't have to manage ties.

request postgresql sql
2 Answers

You can use window function row_number, here is the demo

select
    rnk,
    battlefield_id
from
(

  SELECT
    user_id,
    battlefield_id,
    row_number() over (partition by battlefield_id order by score desc) as rnk 
  FROM scores 
) val
where user_id = 'Aragorn'

Output:

| rnk | battlefield_id |
| --- | -------------- |
| 1   | Helm's Deep    |
| 3   | Pelennor       |
user_86913190

You can use your version with a correlated subquery:

SELECT s.battlefield_id, count(*) AS rank
FROM scores s
WHERE s.score <= (SELECT s2.score
                  FROM scores s2
                  WHERE s2.user_id = 'Aragorn' AND
                        s2.battlefield_id = s.battlefield_id
                 )
GROUP BY s.battlefield_id;

This can take advantage of an index on scores(user_id, battlefield_id). On a large set of data, it would be interesting to compare the performance of this to the row_number() version. This might have a performance advantage.

user_11440350
Related