From 3955c16cdbebddf14accd0fc4ae16776fd809fdd Mon Sep 17 00:00:00 2001 From: Ant Zucaro Date: Thu, 21 Jan 2016 18:46:15 -0500 Subject: [PATCH] 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. --- scripts/update_ranks.sql | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) diff --git a/scripts/update_ranks.sql b/scripts/update_ranks.sql index a985543..575bd2c 100644 --- 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; -- 2.39.2