{"cells":[{"metadata":{"trusted":true},"cell_type":"code","source":"import catboost\nprint(f\"Catboost version: {catboost.__version__}\")\n!python --version","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nfrom catboost import CatBoostClassifier, Pool, cv\nfrom sklearn import model_selection, metrics, preprocessing\n\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"# Stage1\nstage1dir = \"../input/google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\"\nreg_season = pd.read_csv(f\"{stage1dir}/MRegularSeasonDetailedResults.csv\")\ntourney = pd.read_csv(f\"{stage1dir}/MNCAATourneyDetailedResults.csv\")\nsubmit = pd.read_csv(f\"../input/google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MSampleSubmissionStage1_2020.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(f\"Shape: {reg_season.shape}\\n\")\nprint(reg_season.dtypes)\nreg_season.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(f\"Shape: {tourney.shape}\\n\")\nprint(tourney.dtypes)\ntourney.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(f\"Shape: {submit.shape}\\n\")\nprint(submit.dtypes)\nsubmit.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Functions"},{"metadata":{"trusted":true},"cell_type":"code","source":"def get_id_part(ID, part):\n    return {[\"Season\",\"t1\",\"t2\"][i]:v for i,v in enumerate(ID.split(\"_\"))}[part]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Get submission-like dataset from `MRegularSeasonDetailedResults.csv` and a test/validation dataset from `MNCAATourneyDetailedResults.csv`."},{"metadata":{"trusted":true},"cell_type":"code","source":"for i in {\"0\": reg_season,\"test\": tourney}.items():\n    df = pd.DataFrame()\n    label = i[0]\n    idf = i[1]\n    df[\"Season\"] = idf[\"Season\"]\n    df[\"DayNum\"] = idf[\"DayNum\"] if label == \"0\" else 134\n    df[\"t1\"] = idf[[\"WTeamID\",\"LTeamID\"]].min(axis=1)\n    df[\"t2\"] = idf[[\"WTeamID\",\"LTeamID\"]].max(axis=1)\n    df[\"ID\"] = df[\"Season\"].astype(str).add(\"_\").add(df[\"t1\"].astype(str)).add(\"_\").add(df[\"t2\"].astype(str))\n    df[\"Pred\"] = (df[\"t1\"] == idf[\"WTeamID\"]).astype(int)\n    print(f\"{label} Shape: {df.shape}\")\n    print(df.head(20))\n    df.to_csv(f\"df{label}.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Get submission dataset from `MSampleSubmissionStage1_2020.csv`."},{"metadata":{"trusted":true},"cell_type":"code","source":"df = pd.DataFrame()\n\ndf[\"ID\"] = submit[\"ID\"]\ndf[\"Season\"] = submit[\"ID\"].apply(lambda x: get_id_part(x, \"Season\"))\ndf[\"DayNum\"] = 134\ndf[\"t1\"] = submit[\"ID\"].apply(lambda x: get_id_part(x, \"t1\"))\ndf[\"t2\"] = submit[\"ID\"].apply(lambda x: get_id_part(x, \"t2\"))\nprint(f\"Shape: {df.shape}\")\ndf.to_csv(f\"submission.csv\", index=False)\ndf.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Feature engineering"},{"metadata":{},"cell_type":"markdown","source":"## SQL Server query from `MRegularSeasonDetailedResults.csv` to `df.csv` and `MNCAATourneyDetailedResults.csv` to `dftest.csv`\n\n**Why SQL Server?**\n\nBecause Pandas is slow. Using SQL Server this query took 14:54 minutes on a normal laptop. The submission query is the same with two exceptions:\n\n1. `Pred` is not included (of course).\n2. `t1_loc` is just `N` as `'N' AS t1_loc`\n3. `ORDER BY` is changed to `ORDER BY Season, t1, t2`\n\nThe query follows this pattern:\n```\nSELECT <query_1>\n    INTO #temp_table1\n    FROM <original_table>\n\nSELECT <query_2>\n    INTO #temp_table2\n    FROM #temp_table1\n\nSELECT <query_3>\n    FROM #temp_table2\n    \nDROP TABLE #temp_table1\nDROP TABLE #temp_table2\n```\n\n**Here is the full query:**\n\n```\n/****** Script for SelectTopNRows command from SSMS  ******/\nSELECT [Season]\n      ,[DayNum]\n      ,[t1]\n      ,[t2]\n      ,[ID]\n      ,[Pred]\n\t  ,(SELECT CASE WHEN a.t1 = b.WTeamID THEN b.WLoc ELSE\n\t\t\t\t\tCASE WHEN b.WLoc = 'N' THEN 'N' ELSE\n\t\t\t\t\t\tCASE WHEN b.WLoc = 'H' THEN 'A' ELSE 'H' END\n\t\t\t\t\tEND\n\t\t\t   END\n\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\tWHERE b.Season = a.Season AND b.DayNum = a.DayNum AND a.t1 IN (b.WTeamID, b.LTeamID)) AS t1_loc\n\n\t  -- RANKINGS\n\t  ,(SELECT TOP 1 RankingDayNum FROM [kaggle_ncaam_2020].[dbo].[MMasseyOrdinals] b\n\t\t\tWHERE b.Season = a.Season AND b.TeamID = a.t1 ORDER BY ABS(a.DayNum - b.RankingDayNum)) AS t1_rank_day\n\t  ,(SELECT TOP 1 OrdinalRank FROM [kaggle_ncaam_2020].[dbo].[MMasseyOrdinals] b\n\t\t\tWHERE b.Season = a.Season AND b.TeamID = a.t1 ORDER BY ABS(a.DayNum - b.RankingDayNum)) AS t1_rank\n\t  ,(SELECT TOP 1 RankingDayNum FROM [kaggle_ncaam_2020].[dbo].[MMasseyOrdinals] b\n\t\t\tWHERE b.Season = a.Season AND b.TeamID = a.t2 ORDER BY ABS(a.DayNum - b.RankingDayNum)) AS t2_rank_day\n\t  ,(SELECT TOP 1 OrdinalRank FROM [kaggle_ncaam_2020].[dbo].[MMasseyOrdinals] b\n\t\t\tWHERE b.Season = a.Season AND b.TeamID = a.t2 ORDER BY ABS(a.DayNum - b.RankingDayNum)) AS t2_rank\n\n\t  -- T1\n\n\t  --- winning percentage\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 IN (b.WTeamID, b.LTeamID)) AS t1_games\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wins\n\t  , COALESCE(CAST((SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS FLOAT) /\n\t\t\tCAST(NULLIF((SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 IN (b.WTeamID, b.LTeamID)), 0) AS FLOAT), 0.5) AS t1_win_perc\n\n\t  --- home/away/neutral winning percentage\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID AND b.WLoc = 'H') AS t1_H_wins\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID AND b.WLoc = 'A') AS t1_H_losses\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID AND b.WLoc = 'A') AS t1_A_wins\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID AND b.WLoc = 'H') AS t1_A_losses\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID AND b.WLoc = 'N') AS t1_N_wins\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID AND b.WLoc = 'N') AS t1_N_losses\n\n\t  --- outscore\n\t  , (SELECT SUM(b.WScore)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season\n\t\t\tAND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_win_points\n\t  , (SELECT SUM(b.LScore)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season\n\t\t\tAND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_win_opp_points\n\t  , COALESCE((SELECT CAST(SUM(b.WScore) - SUM(b.LScore) AS FLOAT) / CAST(NULLIF(COUNT(b.DayNum), 0) AS FLOAT)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season\n\t\t\tAND b.DayNum < a.DayNum AND a.t1 = b.WTeamID), 0) AS t1_outscore\n\n\t  --- outscored\n\t  , (SELECT SUM(b.LScore)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season\n\t\t\tAND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_l_points\n\t  , (SELECT SUM(b.WScore)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season\n\t\t\tAND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_l_opp_points\n\t  , COALESCE((SELECT CAST(SUM(b.WScore) - SUM(b.LScore) AS FLOAT) / CAST(NULLIF(COUNT(b.DayNum), 0) AS FLOAT)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season\n\t\t\tAND b.DayNum < a.DayNum AND a.t1 = b.LTeamID), 0) AS t1_outscored\n\n\t  --- field goals\n\t  , (SELECT SUM(b.WFGA) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wfga\n\t  , (SELECT SUM(b.WFGM) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wfgm\n\t  , (SELECT CAST(SUM(b.WFGM) AS FLOAT)/NULLIF(CAST(SUM(b.WFGA) AS FLOAT), 0)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wfg_perc\n\n\t  , (SELECT SUM(b.LFGA) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lfga\n\t  , (SELECT SUM(b.LFGM) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lfgm\n\t  , (SELECT CAST(SUM(b.LFGM) AS FLOAT)/NULLIF(CAST(SUM(b.LFGA) AS FLOAT), 0)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lfg_perc\n\t  \n\t  --- 3 pointers\n\t  , (SELECT SUM(b.WFGA3) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wfga3\n\t  , (SELECT SUM(b.WFGM3) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wfgm3\n\t  , (SELECT CAST(SUM(b.WFGM3) AS FLOAT)/NULLIF(CAST(SUM(b.WFGA3) AS FLOAT), 0)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wfg3_perc\n\n\t  , (SELECT SUM(b.LFGA3) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lfga3\n\t  , (SELECT SUM(b.LFGM3) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lfgm3\n\t  , (SELECT CAST(SUM(b.LFGM3) AS FLOAT)/NULLIF(CAST(SUM(b.LFGA3) AS FLOAT), 0)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lfg3_perc\n\n\t  --- free throws\n\t  , (SELECT SUM(b.WFTA) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wfta\n\t  , (SELECT SUM(b.WFTM) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wftm\n\t  , (SELECT CAST(SUM(b.WFTM) AS FLOAT)/NULLIF(CAST(SUM(b.WFTA) AS FLOAT), 0)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wft_perc\n\n\t  , (SELECT SUM(b.LFTA) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lfta\n\t  , (SELECT SUM(b.LFTM) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lftm\n\t  , (SELECT CAST(SUM(b.LFTM) AS FLOAT)/NULLIF(CAST(SUM(b.LFTA) AS FLOAT), 0)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lft_perc\n\n\t  --- rebounds\n\n\t  ---- offensive\n\t  , (SELECT SUM(b.WOR) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wor\n\t  , (SELECT CAST(SUM(b.WOR) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wor_avg\n\n\t  , (SELECT SUM(b.LOR) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lor\n\t  , (SELECT CAST(SUM(b.LOR) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lor_avg\n\n\t  ---- defensive\n\t  , (SELECT SUM(b.WDR) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wdr\n\t  , (SELECT CAST(SUM(b.WDR) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wdr_avg\n\n\t  , (SELECT SUM(b.LDR) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_ldr\n\t  , (SELECT CAST(SUM(b.LDR) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_ldr_avg\n\n\t  --- assists\n\t  , (SELECT SUM(b.WAst) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wast\n\t  , (SELECT CAST(SUM(b.WAst) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wast_avg\n\t  , (SELECT SUM(b.LAst) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_last\n\t  , (SELECT CAST(SUM(b.LAst) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_last_avg\n\n\t  --- timeouts\n\t  , (SELECT SUM(b.WTO) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wto\n\t  , (SELECT CAST(SUM(b.WTO) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wto_avg\n\t  , (SELECT SUM(b.LTO) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lto\n\t  , (SELECT CAST(SUM(b.LTO) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lto_avg\n\n\t  --- steals\n\t  , (SELECT SUM(b.WStl) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wstl\n\t  , (SELECT CAST(SUM(b.WStl) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wstl_avg\n\t  , (SELECT SUM(b.LStl) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lstl\n\t  , (SELECT CAST(SUM(b.LStl) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lstl_avg\n\n\t  --- blocks\n\t  , (SELECT SUM(b.WBlk) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wblk\n\t  , (SELECT CAST(SUM(b.WBlk) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wblk_avg\n\t  , (SELECT SUM(b.LBlk) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lblk\n\t  , (SELECT CAST(SUM(b.LBlk) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lblk_avg\n\n\t  --- personal fouls\n\t  , (SELECT SUM(b.WPF) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wpf\n\t  , (SELECT CAST(SUM(b.WPF) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.WTeamID) AS t1_wpf_avg\n\t  , (SELECT SUM(b.LPF) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lpf\n\t  , (SELECT CAST(SUM(b.LPF) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t1 = b.LTeamID) AS t1_lpf_avg\n\n\t  -- T2\n\n\t  --- winning percentage\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 IN (b.WTeamID, b.LTeamID)) AS t2_games\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wins\n\t  , COALESCE(CAST((SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS FLOAT) /\n\t\t\tCAST(NULLIF((SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 IN (b.WTeamID, b.LTeamID)), 0) AS FLOAT), 0.5) AS t2_win_perc\n\n\t  --- home/away/neutral winning percentage\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID AND b.WLoc = 'H') AS t2_H_wins\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID AND b.WLoc = 'A') AS t2_H_losses\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID AND b.WLoc = 'A') AS t2_A_wins\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID AND b.WLoc = 'H') AS t2_A_losses\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID AND b.WLoc = 'N') AS t2_N_wins\n\t  , (SELECT COUNT(b.DayNum) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID AND b.WLoc = 'N') AS t2_N_losses\n\n\t  --- outscore\n\t  , (SELECT SUM(b.WScore)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season\n\t\t\tAND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_win_points\n\t  , (SELECT SUM(b.LScore)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season\n\t\t\tAND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_win_opp_points\n\t  , COALESCE((SELECT CAST(SUM(b.WScore) - SUM(b.LScore) AS FLOAT) / CAST(NULLIF(COUNT(b.DayNum), 0) AS FLOAT)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season\n\t\t\tAND b.DayNum < a.DayNum AND a.t2 = b.WTeamID), 0) AS t2_outscore\n\n\t  --- outscored\n\t  , (SELECT SUM(b.LScore)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season\n\t\t\tAND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_l_points\n\t  , (SELECT SUM(b.WScore)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season\n\t\t\tAND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_l_opp_points\n\t  , COALESCE((SELECT CAST(SUM(b.WScore) - SUM(b.LScore) AS FLOAT) / CAST(NULLIF(COUNT(b.DayNum), 0) AS FLOAT)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season\n\t\t\tAND b.DayNum < a.DayNum AND a.t2 = b.LTeamID), 0) AS t2_outscored\n\n\t  --- field goals\n\t  , (SELECT SUM(b.WFGA) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wfga\n\t  , (SELECT SUM(b.WFGM) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wfgm\n\t  , (SELECT CAST(SUM(b.WFGM) AS FLOAT)/NULLIF(CAST(SUM(b.WFGA) AS FLOAT), 0)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wfg_perc\n\n\t  , (SELECT SUM(b.LFGA) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lfga\n\t  , (SELECT SUM(b.LFGM) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lfgm\n\t  , (SELECT CAST(SUM(b.LFGM) AS FLOAT)/NULLIF(CAST(SUM(b.LFGA) AS FLOAT), 0)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lfg_perc\n\n\t  --- 3 pointers\n\t  , (SELECT SUM(b.WFGA3) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wfga3\n\t  , (SELECT SUM(b.WFGM3) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wfgm3\n\t  , (SELECT CAST(SUM(b.WFGM3) AS FLOAT)/NULLIF(CAST(SUM(b.WFGA3) AS FLOAT), 0)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wfg3_perc\n\n\t  , (SELECT SUM(b.LFGA3) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lfga3\n\t  , (SELECT SUM(b.LFGM3) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lfgm3\n\t  , (SELECT CAST(SUM(b.LFGM3) AS FLOAT)/NULLIF(CAST(SUM(b.LFGA3) AS FLOAT), 0)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lfg3_perc\n\n\t  --- free throws\n\t  , (SELECT SUM(b.WFTA) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wfta\n\t  , (SELECT SUM(b.WFTM) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wftm\n\t  , (SELECT CAST(SUM(b.WFTM) AS FLOAT)/NULLIF(CAST(SUM(b.WFTA) AS FLOAT), 0)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wft_perc\n\n\t  , (SELECT SUM(b.LFTA) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lfta\n\t  , (SELECT SUM(b.LFTM) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lftm\n\t  , (SELECT CAST(SUM(b.LFTM) AS FLOAT)/NULLIF(CAST(SUM(b.LFTA) AS FLOAT), 0)\n\t\t\tFROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lft_perc\n\n\t  --- rebounds\n\n\t  ---- offensive\n\t  , (SELECT SUM(b.WOR) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wor\n\t  , (SELECT CAST(SUM(b.WOR) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wor_avg\n\n\t  , (SELECT SUM(b.LOR) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lor\n\t  , (SELECT CAST(SUM(b.LOR) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lor_avg\n\n\t  ---- defensive\n\t  , (SELECT SUM(b.WDR) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wdr\n\t  , (SELECT CAST(SUM(b.WDR) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wdr_avg\n\n\t  , (SELECT SUM(b.LDR) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_ldr\n\t  , (SELECT CAST(SUM(b.LDR) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_ldr_avg\n\n\t  --- assists\n\t  , (SELECT SUM(b.WAst) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wast\n\t  , (SELECT CAST(SUM(b.WAst) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wast_avg\n\t  , (SELECT SUM(b.LAst) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_last\n\t  , (SELECT CAST(SUM(b.LAst) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_last_avg\n\n\t  --- timeouts\n\t  , (SELECT SUM(b.WTO) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wto\n\t  , (SELECT CAST(SUM(b.WTO) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wto_avg\n\t  , (SELECT SUM(b.LTO) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lto\n\t  , (SELECT CAST(SUM(b.LTO) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lto_avg\n\n\t  --- steals\n\t  , (SELECT SUM(b.WStl) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wstl\n\t  , (SELECT CAST(SUM(b.WStl) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wstl_avg\n\t  , (SELECT SUM(b.LStl) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lstl\n\t  , (SELECT CAST(SUM(b.LStl) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lstl_avg\n\n\t  --- blocks\n\t  , (SELECT SUM(b.WBlk) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wblk\n\t  , (SELECT CAST(SUM(b.WBlk) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wblk_avg\n\t  , (SELECT SUM(b.LBlk) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lblk\n\t  , (SELECT CAST(SUM(b.LBlk) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lblk_avg\n\n\t  --- personal fouls\n\t  , (SELECT SUM(b.WPF) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wpf\n\t  , (SELECT CAST(SUM(b.WPF) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.WTeamID) AS t2_wpf_avg\n\t  , (SELECT SUM(b.LPF) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lpf\n\t  , (SELECT CAST(SUM(b.LPF) AS FLOAT) / CAST(COUNT(b.DayNum) AS FLOAT) FROM [kaggle_ncaam_2020].[dbo].[MRegularSeasonDetailedResults] b\n\t\t\tWHERE b.Season = a.Season AND b.DayNum < a.DayNum AND a.t2 = b.LTeamID) AS t2_lpf_avg\n  \n  -- Insert into temp table 1.\n  INTO #df1\n\n  FROM [kaggle_ncaam_2020].[dbo].[df0] a\n  ORDER BY Season, t1, DayNum\n\n-- Query from temp table 1 for next processong steps.\nSELECT [Season]\n      ,[DayNum]\n      ,[t1]\n      ,[t2]\n      ,[ID]\n      ,[Pred]\n\t  ,t1_loc\n\n\t  -- RANKINGS\n\t  ,t1_rank_day\n\t  ,t1_rank\n\t  ,t2_rank_day\n\t  ,t2_rank\n\n\t  -- T1\n\n\t  --- wins\n\t  ,t1_games\n\t  ,t1_wins\n\t  ,t1_win_perc\n\n\t  --- home/away/neutral winning percentages\n\t  ,t1_H_wins\n\t  ,t1_H_losses\n\t  ,t1_H_wins + t1_H_losses AS t1_H_games\n\t  ,t1_A_wins\n\t  ,t1_A_losses\n\t  ,t1_A_wins + t1_A_losses AS t1_A_games\n\t  ,t1_N_wins\n\t  ,t1_N_losses\n\t  ,t1_N_wins + t1_N_losses AS t1_N_games\n\n\t  --- outscore\n\t  ,t1_win_points\n\t  ,t1_win_opp_points\n\t  ,t1_outscore\n\n\t  --- outscored\n\t  ,t1_l_points\n\t  ,t1_l_opp_points\n\t  ,t1_outscored\n\n\t  --- points\n\t  ,COALESCE(t1_win_points, 0) + COALESCE(t1_l_points, 0) AS t1_points\n\t  ,(CAST(COALESCE(t1_win_points, 0) AS FLOAT) + CAST(COALESCE(t1_l_points, 0) AS FLOAT)) / NULLIF(t1_games, 0) AS t1_points_avg\n\n\t  --- field goals\n\t  ,t1_wfga\n\t  ,t1_wfgm\n\t  ,t1_wfg_perc\n\t  ,t1_lfga\n\t  ,t1_lfgm\n\t  ,t1_lfg_perc\n\t  , CASE WHEN t1_wfg_perc IS NULL OR t1_lfg_perc IS NULL\n\t\tTHEN (COALESCE(t1_wfg_perc, 0) + COALESCE(t1_lfg_perc, 0))\n\t\tELSE (COALESCE(t1_wfg_perc, 0) + COALESCE(t1_lfg_perc, 0)) / 2\n\t\tEND AS t1_fg_perc\n\n\t  --- 3 pointers\n\t  ,t1_wfga3\n\t  ,t1_wfgm3\n\t  ,t1_wfg3_perc\n\t  ,t1_lfga3\n\t  ,t1_lfgm3\n\t  ,t1_lfg3_perc\n\t  , CASE WHEN t1_wfg3_perc IS NULL OR t1_lfg3_perc IS NULL\n\t\tTHEN (COALESCE(t1_wfg3_perc, 0) + COALESCE(t1_lfg3_perc, 0))\n\t\tELSE (COALESCE(t1_wfg3_perc, 0) + COALESCE(t1_lfg3_perc, 0)) / 2\n\t\tEND AS t1_fg3_perc\n\n\t  --- free throws\n\t  ,t1_wfta\n\t  ,t1_wftm\n\t  ,t1_wft_perc\n\t  ,t1_lfta\n\t  ,t1_lftm\n\t  ,t1_lft_perc\n\t  , CASE WHEN t1_wft_perc IS NULL OR t1_lft_perc IS NULL\n\t\tTHEN (COALESCE(t1_wft_perc, 0) + COALESCE(t1_lft_perc, 0))\n\t\tELSE (COALESCE(t1_wft_perc, 0) + COALESCE(t1_lft_perc, 0)) / 2\n\t\tEND AS t1_ft_perc\n\n\t  --- rebounds\n\n\t  ---- offensive\n\t  ,t1_wor\n\t  ,t1_wor_avg\n\t  ,t1_lor\n\t  ,t1_lor_avg\n\n\t  ----- total offenive\n\t  , CASE WHEN t1_wor_avg IS NULL OR t1_lor_avg IS NULL\n\t\tTHEN (COALESCE(t1_wor_avg, 0) + COALESCE(t1_lor_avg, 0))\n\t\tELSE (COALESCE(t1_wor_avg, 0) + COALESCE(t1_lor_avg, 0)) / 2\n\t\tEND AS t1_or_avg\n\n\t  ---- defensive\n\t  ,t1_wdr\n\t  ,t1_wdr_avg\n\t  ,t1_ldr\n\t  ,t1_ldr_avg\n\n\t  ----- total defensive\n\t  , CASE WHEN t1_wdr_avg IS NULL OR t1_ldr_avg IS NULL\n\t\tTHEN (COALESCE(t1_wdr_avg, 0) + COALESCE(t1_ldr_avg, 0))\n\t\tELSE (COALESCE(t1_wdr_avg, 0) + COALESCE(t1_ldr_avg, 0)) / 2\n\t\tEND AS t1_dr_avg\n\n\t  --- assists\n\t  ,t1_wast\n\t  ,t1_wast_avg\n\t  ,t1_last\n\t  ,t1_last_avg\n\t  , CASE WHEN t1_wast_avg IS NULL OR t1_last_avg IS NULL\n\t\tTHEN (COALESCE(t1_wast_avg, 0) + COALESCE(t1_last_avg, 0))\n\t\tELSE (COALESCE(t1_wast_avg, 0) + COALESCE(t1_last_avg, 0)) / 2\n\t\tEND AS t1_ast_avg\n\n\t  --- timeouts\n\t  ,t1_wto\n\t  ,t1_wto_avg\n\t  ,t1_lto\n\t  ,t1_lto_avg\n\t  , CASE WHEN t1_wto_avg IS NULL OR t1_lto_avg IS NULL\n\t\tTHEN (COALESCE(t1_wto_avg, 0) + COALESCE(t1_lto_avg, 0))\n\t\tELSE (COALESCE(t1_wto_avg, 0) + COALESCE(t1_lto_avg, 0)) / 2\n\t\tEND AS t1_to_avg\n\n\t  --- steals\n\t  ,t1_wstl\n\t  ,t1_wstl_avg\n\t  ,t1_lstl\n\t  ,t1_lstl_avg\n\t  , CASE WHEN t1_wstl_avg IS NULL OR t1_lstl_avg IS NULL\n\t\tTHEN (COALESCE(t1_wstl_avg, 0) + COALESCE(t1_lstl_avg, 0))\n\t\tELSE (COALESCE(t1_wstl_avg, 0) + COALESCE(t1_lstl_avg, 0)) / 2\n\t\tEND AS t1_stl_avg\n\n\t  --- blocks\n\t  ,t1_wblk\n\t  ,t1_wblk_avg\n\t  ,t1_lblk\n\t  ,t1_lblk_avg\n\t  , CASE WHEN t1_wblk_avg IS NULL OR t1_lblk_avg IS NULL\n\t\tTHEN (COALESCE(t1_wblk_avg, 0) + COALESCE(t1_lblk_avg, 0))\n\t\tELSE (COALESCE(t1_wblk_avg, 0) + COALESCE(t1_lblk_avg, 0)) / 2\n\t\tEND AS t1_blk_avg\n\n\t  --- personal fouls\n\t  ,t1_wpf\n\t  ,t1_wpf_avg\n\t  ,t1_lpf\n\t  ,t1_lpf_avg\n\t  , CASE WHEN t1_wpf_avg IS NULL OR t1_lpf_avg IS NULL\n\t\tTHEN (COALESCE(t1_wpf_avg, 0) + COALESCE(t1_lpf_avg, 0))\n\t\tELSE (COALESCE(t1_wpf_avg, 0) + COALESCE(t1_lpf_avg, 0)) / 2\n\t\tEND AS t1_pf_avg\n\n\t  -- T2\n\n\t  --- wins\n\t  ,t2_games\n\t  ,t2_wins\n\t  ,t2_win_perc\n\n\t  --- home/away/neutral winning percentages\n\t  ,t2_H_wins\n\t  ,t2_H_losses\n\t  ,t2_H_wins + t2_H_losses AS t2_H_games\n\t  ,t2_A_wins\n\t  ,t2_A_losses\n\t  ,t2_A_wins + t2_A_losses AS t2_A_games\n\t  ,t2_N_wins\n\t  ,t2_N_losses\n\t  ,t2_N_wins + t2_N_losses AS t2_N_games\n\n\t  --- outscore\n\t  ,t2_win_points\n\t  ,t2_win_opp_points\n\t  ,t2_outscore\n\n\t  --- outscored\n\t  ,t2_l_points\n\t  ,t2_l_opp_points\n\t  ,t2_outscored\n\n\t  --- points\n\t  ,COALESCE(t2_win_points, 0) + COALESCE(t2_l_points, 0) AS t2_points\n\t  ,(CAST(COALESCE(t2_win_points, 0) AS FLOAT) + CAST(COALESCE(t2_l_points, 0) AS FLOAT)) / NULLIF(t2_games, 0) AS t2_points_avg\n\n\t  --- field goals\n\t  ,t2_wfga\n\t  ,t2_wfgm\n\t  ,t2_wfg_perc\n\t  ,t2_lfga\n\t  ,t2_lfgm\n\t  ,t2_lfg_perc\n\t  , CASE WHEN t2_wfg_perc IS NULL OR t2_lfg_perc IS NULL\n\t\tTHEN (COALESCE(t2_wfg_perc, 0) + COALESCE(t2_lfg_perc, 0))\n\t\tELSE (COALESCE(t2_wfg_perc, 0) + COALESCE(t2_lfg_perc, 0)) / 2\n\t\tEND AS t2_fg_perc\n\n\t  --- 3 pointers\n\t  ,t2_wfga3\n\t  ,t2_wfgm3\n\t  ,t2_wfg3_perc\n\t  ,t2_lfga3\n\t  ,t2_lfgm3\n\t  ,t2_lfg3_perc\n\t  , CASE WHEN t2_wfg3_perc IS NULL OR t2_lfg3_perc IS NULL\n\t\tTHEN (COALESCE(t2_wfg3_perc, 0) + COALESCE(t2_lfg3_perc, 0))\n\t\tELSE (COALESCE(t2_wfg3_perc, 0) + COALESCE(t2_lfg3_perc, 0)) / 2\n\t\tEND AS t2_fg3_perc\n\n\t  --- free throws\n\t  ,t2_wfta\n\t  ,t2_wftm\n\t  ,t2_wft_perc\n\t  ,t2_lfta\n\t  ,t2_lftm\n\t  ,t2_lft_perc\n\t  , CASE WHEN t2_wft_perc IS NULL OR t2_lft_perc IS NULL\n\t\tTHEN (COALESCE(t2_wft_perc, 0) + COALESCE(t2_lft_perc, 0))\n\t\tELSE (COALESCE(t2_wft_perc, 0) + COALESCE(t2_lft_perc, 0)) / 2\n\t\tEND AS t2_ft_perc\n\n\t  --- rebounds\n\n\t  ---- offensive\n\t  ,t2_wor\n\t  ,t2_wor_avg\n\t  ,t2_lor\n\t  ,t2_lor_avg\n\n\t  ----- total offenive\n\t  , CASE WHEN t2_wor_avg IS NULL OR t2_lor_avg IS NULL\n\t\tTHEN (COALESCE(t2_wor_avg, 0) + COALESCE(t2_lor_avg, 0))\n\t\tELSE (COALESCE(t2_wor_avg, 0) + COALESCE(t2_lor_avg, 0)) / 2\n\t\tEND AS t2_or_avg\n\n\t  ---- defensive\n\t  ,t2_wdr\n\t  ,t2_wdr_avg\n\t  ,t2_ldr\n\t  ,t2_ldr_avg\n\n\t  ----- total defensive\n\t  , CASE WHEN t2_wdr_avg IS NULL OR t2_ldr_avg IS NULL\n\t\tTHEN (COALESCE(t2_wdr_avg, 0) + COALESCE(t2_ldr_avg, 0))\n\t\tELSE (COALESCE(t2_wdr_avg, 0) + COALESCE(t2_ldr_avg, 0)) / 2\n\t\tEND AS t2_dr_avg\n\n\t  --- assists\n\t  ,t2_wast\n\t  ,t2_wast_avg\n\t  ,t2_last\n\t  ,t2_last_avg\n\t  , CASE WHEN t2_wast_avg IS NULL OR t2_last_avg IS NULL\n\t\tTHEN (COALESCE(t2_wast_avg, 0) + COALESCE(t2_last_avg, 0))\n\t\tELSE (COALESCE(t2_wast_avg, 0) + COALESCE(t2_last_avg, 0)) / 2\n\t\tEND AS t2_ast_avg\n\n\t  --- timeouts\n\t  ,t2_wto\n\t  ,t2_wto_avg\n\t  ,t2_lto\n\t  ,t2_lto_avg\n\t  , CASE WHEN t2_wto_avg IS NULL OR t2_lto_avg IS NULL\n\t\tTHEN (COALESCE(t2_wto_avg, 0) + COALESCE(t2_lto_avg, 0))\n\t\tELSE (COALESCE(t2_wto_avg, 0) + COALESCE(t2_lto_avg, 0)) / 2\n\t\tEND AS t2_to_avg\n\n\t  --- steals\n\t  ,t2_wstl\n\t  ,t2_wstl_avg\n\t  ,t2_lstl\n\t  ,t2_lstl_avg\n\t  , CASE WHEN t2_wstl_avg IS NULL OR t2_lstl_avg IS NULL\n\t\tTHEN (COALESCE(t2_wstl_avg, 0) + COALESCE(t2_lstl_avg, 0))\n\t\tELSE (COALESCE(t2_wstl_avg, 0) + COALESCE(t2_lstl_avg, 0)) / 2\n\t\tEND AS t2_stl_avg\n\n\t  --- blocks\n\t  ,t2_wblk\n\t  ,t2_wblk_avg\n\t  ,t2_lblk\n\t  ,t2_lblk_avg\n\t  , CASE WHEN t2_wblk_avg IS NULL OR t2_lblk_avg IS NULL\n\t\tTHEN (COALESCE(t2_wblk_avg, 0) + COALESCE(t2_lblk_avg, 0))\n\t\tELSE (COALESCE(t2_wblk_avg, 0) + COALESCE(t2_lblk_avg, 0)) / 2\n\t\tEND AS t2_blk_avg\n\n\t  --- personal fouls\n\t  ,t2_wpf\n\t  ,t2_wpf_avg\n\t  ,t2_lpf\n\t  ,t2_lpf_avg\n\t  , CASE WHEN t2_wpf_avg IS NULL OR t2_lpf_avg IS NULL\n\t\tTHEN (COALESCE(t2_wpf_avg, 0) + COALESCE(t2_lpf_avg, 0))\n\t\tELSE (COALESCE(t2_wpf_avg, 0) + COALESCE(t2_lpf_avg, 0)) / 2\n\t\tEND AS t2_pf_avg\n\n  -- Insert into temp table 2.\n  INTO #df2\n\n  FROM #df1\n  ORDER BY Season, t1, DayNum\n\n-- Query from temp table 2 for next processong steps.\nSELECT [Season]\n      ,[DayNum]\n      ,[t1]\n      ,[t2]\n      ,[ID]\n      ,[Pred]\n\t  ,t1_loc\n      ,CHARINDEX(t1_loc, 'HAN') AS t1_loc_enc\n\n\t  -- RANKINGS\n\t  ,t1_rank_day\n\t  ,t1_rank\n\t  ,t2_rank_day\n\t  ,t2_rank\n\n\t  -- T1\n\n\t  --- wins\n\t  ,t1_games\n\t  ,t1_wins\n\t  ,t1_win_perc\n\n\t  --- home/away/neutral winning percentages\n\t  ,t1_H_wins\n\t  ,t1_H_losses\n\t  ,t1_H_games\n\t  ,COALESCE(CAST(t1_H_wins AS FLOAT) / NULLIF(CAST(t1_H_games AS FLOAT), 0), .5) AS t1_H_win_perc\n\t  ,t1_A_wins\n\t  ,t1_A_losses\n\t  ,t1_A_games\n\t  ,COALESCE(CAST(t1_A_wins AS FLOAT) / NULLIF(CAST(t1_A_games AS FLOAT), 0), .5) AS t1_A_win_perc\n\t  ,t1_N_losses\n\t  ,t1_N_games\n\t  ,COALESCE(CAST(t1_N_wins AS FLOAT) / NULLIF(CAST(t1_N_games AS FLOAT), 0), .5) AS t1_N_win_perc\n\n\t  --- outscore\n\t  ,t1_win_points\n\t  ,t1_win_opp_points\n\t  ,t1_outscore\n\n\t  --- outscored\n\t  ,t1_l_points\n\t  ,t1_l_opp_points\n\t  ,t1_outscored\n\n\t  --- points\n\t  ,t1_points\n\t  ,t1_points_avg\n\n\t  --- field goals\n\t  ,t1_wfga\n\t  ,t1_wfgm\n\t  ,t1_wfg_perc\n\t  ,t1_lfga\n\t  ,t1_lfgm\n\t  ,t1_lfg_perc\n\t  ,t1_fg_perc\n\n\t  --- 3 pointers\n\t  ,t1_wfga3\n\t  ,t1_wfgm3\n\t  ,t1_wfg3_perc\n\t  ,t1_lfga3\n\t  ,t1_lfgm3\n\t  ,t1_lfg3_perc\n\t  ,t1_fg3_perc\n\n\t  --- free throws\n\t  ,t1_wfta\n\t  ,t1_wftm\n\t  ,t1_wft_perc\n\t  ,t1_lfta\n\t  ,t1_lftm\n\t  ,t1_lft_perc\n\t  ,t1_ft_perc\n\n\t  --- rebounds\n\n\t  ---- offensive\n\t  ,t1_wor\n\t  ,t1_wor_avg\n\t  ,t1_lor\n\t  ,t1_lor_avg\n\n\t  ----- total offenive\n\t  ,t1_or_avg\n\n\t  ---- defensive\n\t  ,t1_wdr\n\t  ,t1_wdr_avg\n\t  ,t1_ldr\n\t  ,t1_ldr_avg\n\n\t  ----- total defensive\n\t  ,t1_dr_avg\n\n\t  ---- total rebound average\n\t  , (t1_or_avg + t1_dr_avg) / 2 AS t1_r_avg\n\n\t  --- assists\n\t  ,t1_wast\n\t  ,t1_wast_avg\n\t  ,t1_last\n\t  ,t1_last_avg\n\t  ,t1_ast_avg\n\n\t  --- timeouts\n\t  ,t1_wto\n\t  ,t1_wto_avg\n\t  ,t1_lto\n\t  ,t1_lto_avg\n\t  ,t1_to_avg\n\n\t  --- steals\n\t  ,t1_wstl\n\t  ,t1_wstl_avg\n\t  ,t1_lstl\n\t  ,t1_lstl_avg\n\t  ,t1_stl_avg\n\n\t  --- blocks\n\t  ,t1_wblk\n\t  ,t1_wblk_avg\n\t  ,t1_lblk\n\t  ,t1_lblk_avg\n\t  ,t1_blk_avg\n\n\t  --- personal fouls\n\t  ,t1_wpf\n\t  ,t1_wpf_avg\n\t  ,t1_lpf\n\t  ,t1_lpf_avg\n\t  ,t1_pf_avg\n\n\t  -- T2\n\n\t  --- wins\n\t  ,t2_games\n\t  ,t2_wins\n\t  ,t2_win_perc\n\n\t  --- home/away/neutral winning percentages\n\t  ,t2_H_wins\n\t  ,t2_H_losses\n\t  ,t2_H_games\n\t  ,COALESCE(CAST(t2_H_wins AS FLOAT) / NULLIF(CAST(t2_H_games AS FLOAT), 0), .5) AS t2_H_win_perc\n\t  ,t2_A_wins\n\t  ,t2_A_losses\n\t  ,t2_A_games\n\t  ,COALESCE(CAST(t2_A_wins AS FLOAT) / NULLIF(CAST(t2_A_games AS FLOAT), 0), .5) AS t2_A_win_perc\n\t  ,t2_N_losses\n\t  ,t2_N_games\n\t  ,COALESCE(CAST(t2_N_wins AS FLOAT) / NULLIF(CAST(t2_N_games AS FLOAT), 0), .5) AS t2_N_win_perc\n\n\t  --- outscore\n\t  ,t2_win_points\n\t  ,t2_win_opp_points\n\t  ,t2_outscore\n\n\t  --- outscored\n\t  ,t2_l_points\n\t  ,t2_l_opp_points\n\t  ,t2_outscored\n\n\t  --- points\n\t  ,t2_points\n\t  ,t2_points_avg\n\n\t  --- field goals\n\t  ,t2_wfga\n\t  ,t2_wfgm\n\t  ,t2_wfg_perc\n\t  ,t2_lfga\n\t  ,t2_lfgm\n\t  ,t2_lfg_perc\n\t  ,t2_fg_perc\n\n\t  --- 3 pointers\n\t  ,t2_wfga3\n\t  ,t2_wfgm3\n\t  ,t2_wfg3_perc\n\t  ,t2_lfga3\n\t  ,t2_lfgm3\n\t  ,t2_lfg3_perc\n\t  ,t2_fg3_perc\n\n\t  --- free throws\n\t  ,t2_wfta\n\t  ,t2_wftm\n\t  ,t2_wft_perc\n\t  ,t2_lfta\n\t  ,t2_lftm\n\t  ,t2_lft_perc\n\t  ,t2_ft_perc\n\n\t  --- rebounds\n\n\t  ---- offensive\n\t  ,t2_wor\n\t  ,t2_wor_avg\n\t  ,t2_lor\n\t  ,t2_lor_avg\n\n\t  ----- total offenive\n\t  ,t2_or_avg\n\n\t  ---- defensive\n\t  ,t2_wdr\n\t  ,t2_wdr_avg\n\t  ,t2_ldr\n\t  ,t2_ldr_avg\n\n\t  ----- total defensive\n\t  ,t2_dr_avg\n\n\t  ---- total rebound average\n\t  , (t2_or_avg + t2_dr_avg) / 2 AS t2_r_avg\n\n\t  --- assists\n\t  ,t2_wast\n\t  ,t2_wast_avg\n\t  ,t2_last\n\t  ,t2_last_avg\n\t  ,t2_ast_avg\n\n\t  --- timeouts\n\t  ,t2_wto\n\t  ,t2_wto_avg\n\t  ,t2_lto\n\t  ,t2_lto_avg\n\t  ,t2_to_avg\n\n\t  --- steals\n\t  ,t2_wstl\n\t  ,t2_wstl_avg\n\t  ,t2_lstl\n\t  ,t2_lstl_avg\n\t  ,t2_stl_avg\n\n\t  --- blocks\n\t  ,t2_wblk\n\t  ,t2_wblk_avg\n\t  ,t2_lblk\n\t  ,t2_lblk_avg\n\t  ,t2_blk_avg\n\n\t  --- personal fouls\n\t  ,t2_wpf\n\t  ,t2_wpf_avg\n\t  ,t2_lpf\n\t  ,t2_lpf_avg\n\t  ,t2_pf_avg\n\n  FROM #df2\n  ORDER BY Season, t1, DayNum\n\n-- Drop the temp tables.\nDROP TABLE #df1\nDROP TABLE #df2\n```"},{"metadata":{},"cell_type":"markdown","source":"# Get data"},{"metadata":{"trusted":true},"cell_type":"code","source":"df = pd.read_csv(\"../input/ncaam2020/df.csv\")\ndftest = pd.read_csv(\"../input/ncaam2020/dftest.csv\")\ndfsubmit = pd.read_csv(\"../input/ncaam2020/submission.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(f\"df Shape: {df.shape}\\n\")\nprint(\"Columns:\\n\\n\" + \"\\n\".join(df.columns.values))\ndf.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(f\"dftest Shape: {dftest.shape}\\n\")\nprint(\"Columns:\\n\\n\" + \"\\n\".join(dftest.columns.values))\ndftest.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(f\"dfsubmit Shape: {dfsubmit.shape}\\n\")\nprint(\"Columns:\\n\\n\" + \"\\n\".join(dfsubmit.columns.values))\ndfsubmit.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Select features"},{"metadata":{"trusted":true},"cell_type":"code","source":"label = \"Pred\"\ncat_cols = []\n\nfeatures = [\"t1_rank\",\"t2_rank\",\"t1_win_perc\",\"t2_win_perc\",\n            \"t1_outscore\",\"t2_outscore\",\"t1_outscored\",\"t2_outscored\",\n            \"t1_points_avg\",\"t2_points_avg\",\"t1_fg_perc\",\"t2_fg_perc\",\n            \"t1_fg3_perc\",\"t2_fg3_perc\",\"t1_ft_perc\",\"t2_ft_perc\",\n            \"t1_or_avg\",\"t1_dr_avg\",\"t2_or_avg\",\"t2_dr_avg\",\"t1_ast_avg\",\"t2_ast_avg\",\n            \"t1_stl_avg\",\"t2_stl_avg\",\"t1_blk_avg\",\"t2_blk_avg\"]\n\ndf[features].head(10)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Filter out first games"},{"metadata":{"trusted":true},"cell_type":"code","source":"filternum = 0\ndf = df.loc[(df[\"t1_games\"] > filternum) & (df[\"t2_games\"] > filternum)]\nprint(f\"Shape: {df.shape}\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Learn, test, evaluate"},{"metadata":{},"cell_type":"markdown","source":"## Split"},{"metadata":{"trusted":true},"cell_type":"code","source":"X_train = df[features]\nX_test = dftest[features]\ny_train = df[label]\ny_test = dftest[label]\n\ntrain_pool = Pool(X_train, y_train, cat_features=cat_cols)\ntest_pool = Pool(X_test, y_test, cat_features=cat_cols)\npred_pool = Pool(dfsubmit[features], cat_features=cat_cols)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Train, test, evaluate predict"},{"metadata":{"trusted":true},"cell_type":"code","source":"task_type=\"CPU\"\nwaste_time = False\nver = 3\n\nif waste_time:\n    \n    # Grid search\n    model = CatBoostClassifier(loss_function=\"Logloss\", random_seed=1, task_type=task_type)\n\n    grid = {\"learning_rate\": [0.03, 0.1],\n            \"depth\": [4, 6, 10],\n            \"l2_leaf_reg\": [1, 3, 5, 7, 9]}\n\n    grid_search_result = model.grid_search(grid, \n                                           train_pool, \n                                           plot=True)\n    \n    params = grid_search_result[\"params\"]\n    params_df = pd.DataFrame(params, index=[0])\n    \n    # Cross validate\n    params = {\"iterations\": 999,\n              \"depth\":params[\"depth\"],\n              \"l2_leaf_reg\":params[\"l2_leaf_reg\"],\n              \"learning_rate\":params[\"learning_rate\"],\n              \"loss_function\": \"Logloss\",\n              \"random_seed\":1,\n              \"verbose\": True,\n              \"roc_file\": \"roc-file.txt\"}\n\n    scores = cv(train_pool, params)\n    scores.to_csv(\"cv_scores.csv\")\n    \n    iterations = scores.loc[scores[\"test-Logloss-mean\"].idxmin()][\"iterations\"] + 1\n    params_df[\"cv_best_iter\"] = [iterations]\n    \n    # Train\n    model = CatBoostClassifier(\n        iterations=iterations,\n        loss_function=\"Logloss\",\n        random_seed=1,\n        task_type=task_type,\n        depth=params[\"depth\"],\n        l2_leaf_reg=params[\"l2_leaf_reg\"],\n        learning_rate=params[\"learning_rate\"])\n\n    model.fit(train_pool)\n    \n    # Test\n    preds_class = model.predict(test_pool)\n    preds_proba = model.predict_proba(test_pool)\n    print(f\"class = {preds_class[:20]}\")\n    print(f\"proba = {preds_proba[:20]}\")\n    \n    ## Evaluate\n    logloss = metrics.log_loss(y_test, preds_proba)\n    params_df[\"test_logloss\"] = [logloss]\n    params_df.to_csv(\"params.csv\")\n    print(f\"logloss: {logloss}\")\n    \n    # Predict\n    preds_class = model.predict(pred_pool)\n    preds_proba = model.predict_proba(pred_pool)\n    print(f\"class = {preds_class[:20]}\")\n    print(f\"proba = {preds_proba[:20]}\")\n    \n    df = pd.DataFrame({\"ID\":list(dfsubmit[\"ID\"]),\"Pred\":[i[1] for i in preds_proba],\"Class\":preds_class})\n\n    # Submit\n    df.to_csv(f\"sumbit{ver}_full.csv\", index=False)\n    df[[\"ID\",\"Pred\"]].to_csv(f\"sumbit{ver}.csv\", index=False)\n    \nelse:\n    # Get best iteration\n    model = CatBoostClassifier(\n            iterations=999,\n            loss_function=\"Logloss\",\n            random_seed=1,\n            task_type=task_type)\n\n    model.fit(train_pool, eval_set=test_pool)\n    iterations = model.get_best_iteration()\n    \n    iterations = scores.loc[scores[\"test-Logloss-mean\"].idxmin()][\"iterations\"] + 1\n    results_df = pd.DataFrame({\"cv_best_iter\":iterations}, index=[0])\n    \n    # Train\n    model = CatBoostClassifier(\n        iterations=iterations,\n        loss_function=\"Logloss\",\n        random_seed=1,\n        task_type=task_type)\n\n    model.fit(train_pool)\n    \n    # Test\n    preds_class = model.predict(test_pool)\n    preds_proba = model.predict_proba(test_pool)\n    print(f\"class = {preds_class[:20]}\")\n    print(f\"proba = {preds_proba[:20]}\")\n    \n    ## Evaluate\n    logloss = metrics.log_loss(y_test, preds_proba)\n    results_df[\"test_logloss\"] = [logloss]\n    results_df.to_csv(\"results.csv\", index=False)\n    print(f\"logloss: {logloss}\")\n    \n    # Predict\n    preds_class = model.predict(pred_pool)\n    preds_proba = model.predict_proba(pred_pool)\n    print(f\"class = {preds_class[:20]}\")\n    print(f\"proba = {preds_proba[:20]}\")\n    \n    df = pd.DataFrame({\"ID\":list(dfsubmit[\"ID\"]),\"Pred\":[i[1] for i in preds_proba],\"Class\":preds_class})\n\n    # Submit\n    df.to_csv(f\"sumbit{ver}_full.csv\", index=False)\n    df[[\"ID\",\"Pred\"]].to_csv(f\"sumbit{ver}.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(f\"iterations: {iterations}\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(f\"logloss: {logloss}\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Feature importances"},{"metadata":{"trusted":true},"cell_type":"code","source":"fi = model.get_feature_importance(prettified=True)\nfi.to_csv(\"feature_importances.csv\")\nfi","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}