From 87205a30c958e31da05538a5c58c787d98bf8bcd Mon Sep 17 00:00:00 2001
From: Ant Zucaro <azucaro@gmail.com>
Date: Thu, 28 Jan 2016 22:14:05 -0500
Subject: [PATCH] Add script to refresh summary stats.

---
 scripts/refresh_summary_stats_mv.sql | 38 ++++++++++++++++++++++++++++
 1 file changed, 38 insertions(+)
 create mode 100644 scripts/refresh_summary_stats_mv.sql

diff --git a/scripts/refresh_summary_stats_mv.sql b/scripts/refresh_summary_stats_mv.sql
new file mode 100644
index 0000000..6f9c212
--- /dev/null
+++ b/scripts/refresh_summary_stats_mv.sql
@@ -0,0 +1,38 @@
+begin;
+	delete from summary_stats_mv;
+
+	insert into summary_stats_mv
+	-- all time
+	(with active_players as (
+		select count(distinct player_id) num_players 
+		from player_game_stats pgs
+		where pgs.player_id > 2
+	)
+	select 
+	  'all' scope,
+	  row_number() over (order by count(*) desc) rn, 
+	  ap.num_players,
+	  g.game_type_cd, 
+	  count(*)
+	from games g cross join active_players ap
+	group by scope, ap.num_players, g.game_type_cd)
+
+	-- daily
+	UNION
+	(with active_players as (
+		select count(distinct player_id) num_players 
+		from player_game_stats pgs
+		where pgs.player_id > 2
+		and pgs.create_dt >= now() at time zone 'utc' - interval '1 day'
+	)
+	select 
+	  'day' scope,
+	  row_number() over (order by count(*) desc) rn, 
+	  ap.num_players,
+	  g.game_type_cd, 
+	  count(*)
+	from games g cross join active_players ap
+	where g.create_dt >= now() at time zone 'utc' - interval '1 day'
+	group by scope, ap.num_players, g.game_type_cd);
+
+end;
-- 
2.39.2