Add win ratio query
authorAnt Zucaro <azucaro@gmail.com>
Mon, 31 Dec 2012 03:45:33 +0000 (22:45 -0500)
committerAnt Zucaro <azucaro@gmail.com>
Mon, 31 Dec 2012 03:45:33 +0000 (22:45 -0500)
queries/win_ratio_by_player_id.sql [new file with mode: 0644]

diff --git a/queries/win_ratio_by_player_id.sql b/queries/win_ratio_by_player_id.sql
new file mode 100644 (file)
index 0000000..09bf360
--- /dev/null
@@ -0,0 +1,21 @@
+-- win ratio per player, per game type
+SELECT game_type_cd,
+       SUM(win),
+       SUM(loss)
+FROM   (SELECT g.game_id,
+               g.game_type_cd,
+               CASE
+                 WHEN g.winner = pgs.team THEN 1
+                 WHEN pgs.rank = 1 THEN 1
+                 ELSE 0
+               END win,
+               CASE
+                 WHEN g.winner = pgs.team THEN 0
+                 WHEN pgs.rank = 1 THEN 0
+                 ELSE 1
+               END loss
+        FROM   games g,
+               player_game_stats pgs
+        WHERE  g.game_id = pgs.game_id
+               AND pgs.player_id = 6) win_loss
+GROUP  BY game_type_cd;