Skip to content

Instantly share code, notes, and snippets.

@leafo
Last active August 29, 2015 13:57
Show Gist options
  • Save leafo/9524211 to your computer and use it in GitHub Desktop.
Save leafo/9524211 to your computer and use it in GitHub Desktop.
select
jam_users.user_id,
games.title,
jam_users.rated_count - jam_games.rating_count as score,
(jam_users.rated_count - jam_games.rating_count)::float / (jam_games.rating_count + 1) as score2,
jam_users.rated_count as rated,
jam_games.rating_count as ratings
from jam_games
inner join games on games.id = jam_games.game_id
inner join jam_users on jam_users.user_id = games.user_id and jam_users.jam_id = jam_games.jam_id
where jam_games.jam_id = 3 order by score2 desc limit 20;
user_id | title | score | score2 | rated | ratings
---------+------------------------+-------+------------------+-------+---------
8000 | Renegade | 38 | 38 | 38 | 0
7677 | End of Line | 19 | 19 | 19 | 0
1896 | Terminal Run | 17 | 17 | 17 | 0
7786 | ICEbreaker | 41 | 8.2 | 45 | 4
7755 | Azeban Neon | 90 | 8.18181818181818 | 100 | 10
1896 | Terminal Run | 16 | 8 | 17 | 1
151 | cyberdekay | 6 | 6 | 6 | 0
7618 | Runner | 6 | 6 | 6 | 0
7797 | HARDWIRED | 11 | 5.5 | 12 | 1
1654 | Burning of Time | 43 | 5.375 | 50 | 7
7451 | Cyber Terminal | 10 | 5 | 11 | 1
7573 | Neon City Runner | 14 | 4.66666666666667 | 16 | 2
7670 | Random Access Memories | 17 | 4.25 | 20 | 3
6057 | REALITYNET | 16 | 4 | 19 | 3
7763 | Aeroglitch | 8 | 4 | 9 | 1
7020 | Neon Sky | 8 | 4 | 9 | 1
6691 | WarRunner | 8 | 4 | 9 | 1
4669 | Mech Stroller | 35 | 3.5 | 44 | 9
7457 | CRASH | 20 | 3.33333333333333 | 25 | 5
7097 | Expmod | 19 | 3.16666666666667 | 24 | 5
(20 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment