From: Ant Zucaro <azucaro@gmail.com>
Date: Sat, 30 Jan 2016 19:48:17 +0000 (-0500)
Subject: Add script to refresh the active players MV.
X-Git-Url: http://de.git.xonotic.org/?p=xonotic%2Fxonstatdb.git;a=commitdiff_plain;h=6133b6357ce5bcb03e4f47121f72f89ddb835f38

Add script to refresh the active players MV.
---

diff --git a/scripts/refresh_active_players_mv.sql b/scripts/refresh_active_players_mv.sql
new file mode 100644
index 0000000..c3064d9
--- /dev/null
+++ b/scripts/refresh_active_players_mv.sql
@@ -0,0 +1,11 @@
+begin;
+	delete from active_players_mv;
+
+	insert into active_players_mv
+	select row_number() over(order by sum(pgs.alivetime) desc) sort_order, p.player_id, p.nick, sum(pgs.alivetime) alivetime
+	from players p join player_game_stats pgs on p.player_id = pgs.player_id
+	where pgs.create_dt >= (now() at time zone 'UTC' - interval '1 week')
+	and p.active_ind = true
+	and p.player_id > 2
+	group by 2, 3;
+end;