A simple dashboard report for providing high level stats...about stats!
authorAnt Zucaro <azucaro@gmail.com>
Tue, 20 Mar 2012 12:41:40 +0000 (08:41 -0400)
committerAnt Zucaro <azucaro@gmail.com>
Tue, 20 Mar 2012 12:41:40 +0000 (08:41 -0400)
scripts/dashboard_report.sql [new file with mode: 0755]

diff --git a/scripts/dashboard_report.sql b/scripts/dashboard_report.sql
new file mode 100755 (executable)
index 0000000..43e243a
--- /dev/null
@@ -0,0 +1,43 @@
+-- count of games for the given year
+select date_part('month', create_dt), count(*) 
+from games 
+where date_part('year', create_dt) = 2012 
+group by date_part('month', create_dt) 
+order by date_part('month', create_dt);
+
+-- count of game types for the given year
+select game_type_cd, count(*) 
+from games 
+where date_part('year', create_dt) = 2012 
+group by game_type_cd 
+order by count(*) desc;
+
+-- count of unique players playing in the given month
+select date_part('month', create_dt), count(distinct player_id)
+from player_game_stats
+where date_part('year', create_dt) = 2012 
+group by date_part('month', create_dt) 
+order by date_part('month', create_dt);
+
+-- count of servers with the most games
+select servers.name, count(*)
+from servers, games
+where servers.server_id = games.server_id
+and date_part('year', games.create_dt) = 2012 
+group by servers.name
+order by count(*) desc;
+
+-- count of maps with the most games
+select maps.name, count(*)
+from games, maps
+where maps.map_id = games.map_id
+and date_part('year', games.create_dt) = 2012 
+group by maps.name
+order by count(*) desc;
+
+-- new players by month
+select date_part('month', create_dt), count(*)
+from players
+where date_part('year', create_dt) = 2012
+group by date_part('month', create_dt)
+order by date_part('month', create_dt);