Add a script to refresh active servers.
authorAnt Zucaro <azucaro@gmail.com>
Sat, 30 Jan 2016 19:53:53 +0000 (14:53 -0500)
committerAnt Zucaro <azucaro@gmail.com>
Sat, 30 Jan 2016 19:53:53 +0000 (14:53 -0500)
scripts/refresh_active_servers_mv.sql [new file with mode: 0644]

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