queries/win_ratio_by_player_id.sql

   1 -- win ratio per player, per game type
   2 SELECT game_type_cd,
   3        SUM(win),
   4        SUM(loss)
   5 FROM   (SELECT g.game_id,
   6                g.game_type_cd,
   7                CASE
   8                  WHEN g.winner = pgs.team THEN 1
   9                  WHEN pgs.rank = 1 THEN 1
  10                  ELSE 0
  11                END win,
  12                CASE
  13                  WHEN g.winner = pgs.team THEN 0
  14                  WHEN pgs.rank = 1 THEN 0
  15                  ELSE 1
  16                END loss
  17         FROM   games g,
  18                player_game_stats pgs
  19         WHERE  g.game_id = pgs.game_id
  20                AND pgs.player_id = 6) win_loss
  21 GROUP  BY game_type_cd;