Exclude rankings for those at the Elo floor.

Those with an Elo value of 100 (the floor) will no longer be ranked.
Additionally, there will no longer be shared ranks. If two players happen to
have the exact same Elo value (unlikely), the player whose Elo record was
created first will be given the higher rank. This rewards players who have
stuck around for a long time, should they tie with someone else who hasn't been
around as long.

diff --git a/scripts/update_ranks.sql b/scripts/update_ranks.sql
index a985543a37f368fd034557a5dda2f863c974da76..575bd2c4b19f91a1b8283c592eb69e412724e9e5 100644 (file)
--- a/scripts/update_ranks.sql
+++ b/scripts/update_ranks.sql
@@ -8,12 +8,13 @@ begin;
     delete from player_ranks;
 
     insert into player_ranks(player_id, nick, game_type_cd, elo, rank)
-    select p.player_id, p.nick, pe.game_type_cd, pe.elo, rank() 
-    over (partition by pe.game_type_cd order by pe.elo desc)
+    select p.player_id, p.nick, pe.game_type_cd, pe.elo, row_number() 
+    over (partition by pe.game_type_cd order by pe.elo desc, pe.create_dt)
     from players p, player_elos pe
     where p.player_id = pe.player_id
     and p.active_ind = True
     and pe.active_ind = True
-    and pe.games >= 32;
+    and pe.games >= 32
+    and pe.elo > 100;
 
 end;