calculating windowed rank in postgresql for a scoreboard or leaderboard

window_rank.sql

-- 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 
WHERE rank <= (select rank from global_rank where name = 'user_3')+3 
AND   rank >= (select rank from global_rank where name = 'user_3')-3;

-- output:
-- username,score,rank
-- "user_7";75462;5
-- "user_6";64564;6
-- "user_2";63456;7
-- "user_3";35643;8
-- "user_16";16687;9
-- "user_15";15435;10
-- "user_19";14569;11