DROP TABLE IF EXISTS WIN_MATRIX
;

CREATE TABLE WIN_MATRIX AS (
  SELECT SUBSTR(E.GAME_ID,4,4)			AS YEAR_ID
        ,(CASE WHEN (E.INN_CT>9)
              THEN 10
              ELSE E.INN_CT
          END)					AS INN_CT
        ,E.BAT_HOME_ID				AS HOME_ID
        ,E.OUTS_CT				AS OUTS_CT
        ,(CASE WHEN (E.BASE1_RUN_ID != '')
               THEN 1
               ELSE 0
          END)					AS RUN1_FL
        ,(CASE WHEN (E.BASE2_RUN_ID != '')
               THEN 1
               ELSE 0
          END)					AS RUN2_FL
        ,(CASE WHEN (E.BASE3_RUN_ID != '')
               THEN 1
               ELSE 0
           END)					AS RUN3_FL
        ,(CASE WHEN (E.BAT_HOME_ID = 0)
               THEN E.AWAY_SCORE_CT - E.HOME_SCORE_CT
               ELSE E.HOME_SCORE_CT - E.AWAY_SCORE_CT
          END)					AS RUN_DIFF
        ,SUM( CASE WHEN (G.WINLOSS_FL = 1)
                   THEN 1
                   ELSE 0
               END )  				AS WINS
        ,COUNT(1) 				AS N
  FROM EVENTS E
  INNER JOIN GAMES_SCORE G ON ( E.GAME_ID         = G.GAME_ID 
                                AND E.BAT_HOME_ID = G.HOME_ID )
  GROUP BY YEAR_ID,E.INN_CT,E.OUTS_CT,E.BAT_HOME_ID,RUN1_FL,RUN2_FL,RUN3_FL,RUN_DIFF
)
;

CREATE INDEX WIN_MATRIX_IDX 
    ON WIN_MATRIX 
       ( YEAR_ID,INN_CT,OUTS_CT,HOME_ID,RUN1_FL,RUN2_FL,RUN3_FL,RUN_DIFF )
;