Skip to content

Instantly share code, notes, and snippets.

@jatorre

jatorre/gist:1854494

Created February 17, 2012 17:30
Show Gist options
  • Save jatorre/1854494 to your computer and use it in GitHub Desktop.
Save jatorre/1854494 to your computer and use it in GitHub Desktop.
Download ZIP
My first use of rank window functions
Raw
gistfile1.sql
SELECT *, rank() OVER (PARTITION BY challenge_id ORDER BY user_num_captures DESC) AS rank,
(SELECT COUNT(*) FROM captures WHERE  challenge_id = subq.challenge_id) as total_captures
FROM (SELECT challenge_id,ch.title, player_id,count(*) AS user_num_captures FROM captures AS ca INNER JOIN challenges AS ch ON ca.challenge_id=ch.cartodb_id GROUP BY challenge_id,ch.title,player_id) AS subq
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment