From a7f96f48f2b43ce943bd767b71a48c8af749d9c5 Mon Sep 17 00:00:00 2001 From: Ant Zucaro Date: Sun, 30 Dec 2012 22:45:33 -0500 Subject: [PATCH] Add win ratio query --- queries/win_ratio_by_player_id.sql | 21 +++++++++++++++++++++ 1 file changed, 21 insertions(+) create mode 100644 queries/win_ratio_by_player_id.sql diff --git a/queries/win_ratio_by_player_id.sql b/queries/win_ratio_by_player_id.sql new file mode 100644 index 0000000..09bf360 --- /dev/null +++ b/queries/win_ratio_by_player_id.sql @@ -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; -- 2.39.2