Merge branch 'add-ok-weapons' into 'master'

[xonotic/xonstatdb.git] / scripts / update_ranks.sql

diff --git a/scripts/update_ranks.sql b/scripts/update_ranks.sql
index 66e8b12fc1dce5d2688216ace66bb50a58adda41..2f34040e417a912664ae173dff997b7067d71068 100644 (file)
--- a/scripts/update_ranks.sql
+++ b/scripts/update_ranks.sql
@@ -1,10 +1,20 @@
 begin;
+    -- save the history
+    insert into player_ranks_history
+    select * from player_ranks;
+
+    -- get rid of the existing ranks and refresh them using
+    -- the latest elo information for each game type
     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;
+    where p.player_id = pe.player_id
+    and p.active_ind = True
+    and pe.active_ind = True
+    --and pe.games >= 32
+    and pe.elo > 100;
 
 end;