How to calculate a rank in Neo4j

Question

I am having two types of nodes (game & player) and one relationship (PLAYED). PLAYED relationship is having a property 'points'.
Sample Data:
Player (309274) scored 10 points
Player (309275) scored 20 points
Player (309276) scored 30 points
Player (309277) scored 40 points
Player (309278) scored 50 points

I want to calculate a rank of a Player 309278 i.e. 5 from the cypher query. Can anybody help me here to generate cypher query?

Answer 1

MATCH (p:Player)-[pl:PLAYED]->(:Game {id:{game-id}})
RETURN p.name
ORDER BY pl.score desc

then the row-number is your rank, which your client can compute easily

to access a certain rank:

MATCH (p:Player)-[pl:PLAYED]->(:Game {id:{game-id}})
RETURN p.name
ORDER BY pl.score desc
SKIP 4 LIMIT 1

to compute the rank, you'd probably, do something like this (not efficient):

MATCH (p:Player)-[pl:PLAYED]->(:Game {id:{game-id}})
WITH p,pl
ORDER BY pl.score desc
// create a a collection
WITH collect(p) as players
UNWIND reduce(acc=[],idx in range(0,size(players)-1) | 
                     acc + [idx+1,players[idx]]) as player_rank
RETURN player_rank

Answer 2

Based on Michael Hunger's answer, the following query computes the rank and returns the player and their rank on the same row instead of on alternating rows:

MATCH (p:Player)-[pl:PLAYED]->(:Game {id:{game-id}})
WITH p, pl
ORDER BY pl.score desc
WITH collect(p) as players
MATCH (p2:Player)-[pl2:PLAYED]->(:Game {id:{game-id}})
RETURN p2, size(filter(x in players where pl2.score < x.score)) + 1

The way it works is that after ordering the players by their score, then - for each player p2 - it counts the number of players that scored higher, which equals p2's 0-based rank.

The caveat is that, with a complexity of O(n²), it's very inefficient if the number of players is large. The advantage is that it allows for further processing of the rank in relation to the player, e.g. store their rank on the relationship:

SET pl2.rank = 1 + size(filter(x in players where pl2.score < x.score))

which can be queried later on without the need for futher calculations:

MATCH (p:Player)-[pl:PLAYED]->(:Game {id:{game-id}})
RETURN pl.rank