This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- this SQL can be used to calculate the rank of a given user in a game, | |
-- and the names/scores of those just above and below him. | |
-- This is useful in online games or citizen science projects where you | |
-- just want to see the 'proximity' of other users around you, not the entire global rank | |
-- I want to find the rank and score for user_3, and other users 3 above and 3 below. | |
WITH global_rank AS ( | |
SELECT name, score, rank() OVER (ORDER BY score DESC) FROM scores | |
) | |
SELECT * FROM global_rank |