Add script to refresh the active players MV.
authorAnt Zucaro <azucaro@gmail.com>
Sat, 30 Jan 2016 19:48:17 +0000 (14:48 -0500)
committerAnt Zucaro <azucaro@gmail.com>
Sat, 30 Jan 2016 19:48:17 +0000 (14:48 -0500)
scripts/refresh_active_players_mv.sql [new file with mode: 0644]

diff --git a/scripts/refresh_active_players_mv.sql b/scripts/refresh_active_players_mv.sql
new file mode 100644 (file)
index 0000000..c3064d9
--- /dev/null
@@ -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;