From: Ant Zucaro <azucaro@gmail.com>
Date: Sat, 30 Jan 2016 19:53:53 +0000 (-0500)
Subject: Add a script to refresh active servers.
X-Git-Url: https://git.xonotic.org/?p=xonotic%2Fxonstatdb.git;a=commitdiff_plain;h=8d61f30b9b3743a6244da2dac31b5ba4eb84d6b3

Add a script to refresh active servers.
---

diff --git a/scripts/refresh_active_servers_mv.sql b/scripts/refresh_active_servers_mv.sql
new file mode 100644
index 0000000..219a94d
--- /dev/null
+++ b/scripts/refresh_active_servers_mv.sql
@@ -0,0 +1,9 @@
+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;
+end;