From 6efba9e995f5ba2cfd0c76332c4b1a17985b7c4c Mon Sep 17 00:00:00 2001 From: Ant Zucaro Date: Tue, 7 Nov 2017 21:19:21 -0500 Subject: [PATCH] Update the refresh script for active servers. --- scripts/refresh_active_servers_mv.sql | 19 ++++++++++++++----- 1 file changed, 14 insertions(+), 5 deletions(-) diff --git a/scripts/refresh_active_servers_mv.sql b/scripts/refresh_active_servers_mv.sql index 219a94d..05bdf7b 100644 --- a/scripts/refresh_active_servers_mv.sql +++ b/scripts/refresh_active_servers_mv.sql @@ -1,9 +1,18 @@ -begin; +do $$ +declare + cutoff timestamp := now() at time zone 'UTC' - interval '1 week'; +begin delete from active_servers_mv; insert into active_servers_mv - select row_number() over(order by count(1) desc) sort_order, s.server_id, s.name, count(1) games - from servers s join games g on s.server_id = g.server_id - where g.create_dt >= now() at time zone 'UTC' - interval '1 week' - group by 2, 3; + select row_number() over (order by sum(least(g.duration, pgs.alivetime)) desc) sort_order, + s.server_id, s.name, sum(least(g.duration, pgs.alivetime)) play_time + from servers s join games g on g.server_id = s.server_id + join player_game_stats pgs on g.game_id = pgs.game_id + where pgs.player_id > 1 + and g.create_dt > cutoff + and pgs.create_dt > cutoff + group by s.server_id, s.name + order by 1; end; +$$ -- 2.39.2