Using xG & advanced stats to predict football matches

create or replace table betting_dv.football_match_his_l_s_understat_league_stats_sma as
/*
 * 500 matches are take to calculate the avg goals for a division
 */
select
  his.football_match_his_lid,
  -----
  --normal goals
  --SMAs for home goals for
  round(avg(stats.home_goals) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) home_goals_for_sma500,
  --SMAs for home goals against
  round(avg(stats.away_goals) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) home_goals_against_sma500,
  --SMAs for away goals fo
  round(avg(stats.away_goals) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) away_goals_for_sma500,
  --SMAs for away goals against
  round(avg(stats.home_goals) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) away_goals_against_sma500,
  ------
  --expected goals
  --SMAs for home xgoals for
  round(avg(stats.home_xgoals) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) home_xg_for_sma500,
  --SMAs for home xgoals against
  round(avg(stats.away_xgoals) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) home_xg_against_sma500,
  --SMAs for away xgoals for
  round(avg(stats.away_xgoals) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) away_xg_for_sma500,
  --SMAs for away xgoals against
  round(avg(stats.home_xgoals) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) away_xg_against_sma500,
  --shots
  --SMAs for home shots for
  round(avg(stats2.home_shots) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) home_shots_for_sma500,
  --SMAs for home shots against
  round(avg(stats2.away_shots) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) home_shots_against_sma500,
  --SMAs for away shots for
  round(avg(stats2.away_shots) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) away_shots_for_sma500,
  --SMAs for away shots against
  round(avg(stats2.home_shots) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) away_shots_against_sma500,
  --shots_on_target
  --SMAs for home shots_on_target for
  round(avg(stats2.home_shots_target) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) home_shots_on_target_for_sma500,
  --SMAs for home shots_on_target against
  round(avg(stats2.away_shots_target) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) home_shots_on_target_against_sma500,
  --SMAs for away shots_on_target for
  round(avg(stats2.away_shots_target) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) away_shots_on_target_for_sma500,
  --SMAs for away shots_on_target against
  round(avg(stats2.home_shots_target) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) away_shots_on_target_against_sma500,
  --ppda
  --SMAs for home ppda for
  round(avg(stats.home_ppda) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) home_ppda_for_sma500,
  --SMAs for home ppda against
  round(avg(stats.away_ppda) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) home_ppda_against_sma500,
  --SMAs for away ppda for
  round(avg(stats.away_ppda) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) away_ppda_for_sma500,
  --SMAs for away ppda against
  round(avg(stats.home_ppda) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) away_ppda_against_sma500,
  --deep
  --SMAs for home deep for
  round(avg(stats.home_deep) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) home_deep_for_sma500,
  --SMAs for home deep against
  round(avg(stats.away_deep) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) home_deep_against_sma500,
  --SMAs for away deep for
  round(avg(stats.away_deep) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) away_deep_for_sma500,
  --SMAs for away deep against
  round(avg(stats.home_deep) over (partition by division order by match_date rows between 500 preceding and current row exclude current row),1) away_deep_against_sma500
from
  betting_dv.football_match_his_b his
  join raw_dv.football_match_his_l_s_understat_team_stats stats on
    his.football_match_his_lid = stats.football_match_his_lid
  JOIN RAW_DV.FOOTBALL_MATCH_HIS_L_S_STATISTIC stats2
		ON his.FOOTBALL_MATCH_HIS_LID = stats2.FOOTBALL_MATCH_HIS_LID
;

with
home_his as
(
/**
get historic matches for home team
statistics are projected for 1 match in the past,
as the result of the current match should not be part of
the prediction features
**/
select
  his.football_match_his_lid,
  his.home_team,
  his.match_date,
  --home stats
  stats.home_goals 				home_goals_for,
  stats.away_goals				home_goals_against,
  stats.home_xgoals 			home_xg_for,
  stats.away_xgoals 			home_xg_against,
  stats2.home_shots				home_shots_for,
  stats2.AWAY_SHOTS				home_shots_against,
  stats2.HOME_SHOTS_TARGET 	home_shots_on_target_for,
  stats2.AWAY_SHOTS_TARGET 	home_shots_on_target_against,
  stats.HOME_PPDA 				home_ppda_for,
  stats.AWAY_PPDA 				home_ppda_against,
  stats.HOME_DEEP 				home_deep_for,
  stats.AWAY_DEEP 				home_deep_against
from
  (
  select
    his.football_match_his_lid,
    his.match_date,
    his.home_team,
    his.away_team,
    lead(football_match_his_lid) over (partition by his.home_team order by his.match_date desc) prev_football_match_his_lid
  from
    betting_dv.football_match_his_b his
  ) his
  join raw_dv.football_match_his_l_s_understat_team_stats stats
    on his.prev_football_match_his_lid = stats.football_match_his_lid
  JOIN RAW_DV.FOOTBALL_MATCH_HIS_L_S_STATISTIC stats2
		ON his.FOOTBALL_MATCH_HIS_LID = stats2.FOOTBALL_MATCH_HIS_LID
),
away_his as
(  
/**
get historic matches for away team
statistics are projected for 1 match in the past,
as the result of the current match should not be part of
the prediction features
**/
select
  his.football_match_his_lid,
  his.away_team,
  his.match_date,
  --away stats
  stats.away_goals 				away_goals_for,
  stats.home_goals				away_goals_against,
  stats.away_xgoals 			away_xg_for,
  stats.home_xgoals 			away_xg_against,
  stats2.home_shots				away_shots_for,
  stats2.AWAY_SHOTS				away_shots_against,
  stats2.HOME_SHOTS_TARGET 	away_shots_on_target_for,
  stats2.AWAY_SHOTS_TARGET 	away_shots_on_target_against,
  stats.away_PPDA 				away_ppda_for,
  stats.home_PPDA 				away_ppda_against,
  stats.away_DEEP 				away_deep_for,
  stats.home_DEEP 				away_deep_against
from
  (
  select
    his.football_match_his_lid,
    his.match_date,
    his.home_team,
    his.away_team,
    lead(football_match_his_lid) over (partition by his.away_team order by his.match_date desc) prev_football_match_his_lid
  from
    betting_dv.football_match_his_b his
  ) his
  join raw_dv.football_match_his_l_s_understat_team_stats stats
    on his.prev_football_match_his_lid = stats.football_match_his_lid
  JOIN RAW_DV.FOOTBALL_MATCH_HIS_L_S_STATISTIC stats2
		ON his.FOOTBALL_MATCH_HIS_LID = stats2.FOOTBALL_MATCH_HIS_LID
)

home_goals_for_ema15 as
(
select
  betting_dv.ema(home_team, match_date, home_goals_for, 15)  
from
  home_his
group by
  home_team
)

WITH mvd_stats
AS
(
/**
get historic matches for teams
statistics are projected for 1 match in the past,
as the result of the current match should not be part of
the prediction features,
not difference between home and away
**/
SELECT 
	his.football_match_his_lid,
	his.MATCH_DATE,
	--get next match, to exclude stats for current one
	--stats are moved by one match
	lag(his.football_match_his_lid) over (partition by t.FOOTBALL_TEAM_HID order by his.match_date desc) next_football_match_his_lid,
	lag(his.MATCH_DATE) OVER (PARTITION BY t.FOOTBALL_TEAM_HID ORDER BY his.MATCH_DATE desc) next_match_date,
	lead(his.football_match_his_lid) over (partition by t.FOOTBALL_TEAM_HID order by his.match_date desc) prev_football_match_his_lid,
	lead(his.MATCH_DATE) OVER (PARTITION BY t.FOOTBALL_TEAM_HID ORDER BY his.MATCH_DATE desc) prev_match_date,
	--
	his.FOOTBALL_TEAM_HOME_HID, 
	his.HOME_TEAM,
	his.FOOTBALL_TEAM_AWAY_HID,
	his.AWAY_TEAM, 
	--
	t.FOOTBALL_TEAM_HID,
	t.TEAM ,
	CASE 
		WHEN t.FOOTBALL_TEAM_HID = his.FOOTBALL_TEAM_HOME_HID THEN 'h'
		WHEN t.FOOTBALL_TEAM_HID = his.FOOTBALL_TEAM_AWAY_HID THEN 'a'
		ELSE 'n.a.'
	END home_away,
	CASE WHEN LOCAL.home_away = 'h' THEN s1.home_goals else s1.away_goals END						goals_for,
	CASE WHEN LOCAL.home_away = 'h' THEN s1.away_goals else s1.home_goals END						goals_against,
	CASE WHEN LOCAL.home_away = 'h' THEN s1.home_xgoals else s1.away_xgoals END						xg_for,
	CASE WHEN LOCAL.home_away = 'h' THEN s1.away_xgoals else s1.home_xgoals END						xg_against,
	CASE WHEN LOCAL.home_away = 'h' THEN s1.home_npxg else s1.away_npxg END							npxg_for,
	CASE WHEN LOCAL.home_away = 'h' THEN s1.away_npxg else s1.home_npxg END							npxg_against,
	CASE WHEN LOCAL.home_away = 'h' THEN s2.home_shots else s2.away_shots END						shots_for,
	CASE WHEN LOCAL.home_away = 'h' THEN s2.away_shots else s2.home_shots END						shots_against,
	CASE WHEN LOCAL.home_away = 'h' THEN s2.home_shots_target else s2.away_shots_target END			shots_target_for,
	CASE WHEN LOCAL.home_away = 'h' THEN s2.away_shots_target else s2.home_shots_target END			shots_target_against,
	CASE WHEN LOCAL.home_away = 'h' THEN s1.home_deep else s1.away_deep END							deep_for,
	CASE WHEN LOCAL.home_away = 'h' THEN s1.away_deep else s1.home_deep END							deep_against,
	CASE WHEN LOCAL.home_away = 'h' THEN s1.home_ppda else s1.away_ppda END							ppda_for,
	CASE WHEN LOCAL.home_away = 'h' THEN s1.away_ppda else s1.home_ppda END							ppda_against
from
	betting_dv.football_match_his_b his
	JOIN RAW_DV.FOOTBALL_MATCH_HIS_L_S_UNDERSTAT_TEAM_STATS s1
		ON his.FOOTBALL_MATCH_HIS_LID = s1.FOOTBALL_MATCH_HIS_LID
	JOIN RAW_DV.FOOTBALL_MATCH_HIS_L_S_STATISTIC s2
		ON his.FOOTBALL_MATCH_HIS_LID = s2.FOOTBALL_MATCH_HIS_LID
	--join team for away and home to get
	--complete historic matches
	JOIN RAW_DV.FOOTBALL_TEAM_H t
		ON his.FOOTBALL_TEAM_HOME_HID = t.FOOTBALL_TEAM_HID 
		OR his.FOOTBALL_TEAM_AWAY_HID = t.FOOTBALL_TEAM_HID
)

import pyexasol
import pandas as pd
import matplotlib.pyplot as plt
from sklearn.ensemble import RandomForestClassifier


dwh_host = '[host-name]'
dwh_port = '8563'
dwh_user = '[DB-user]'
dwh_pass = '[DB-password]'

v_sql = """-- BETTING_MART.UNDERSTAT_TEAM_MODEL_FEATURES_EMA15 source
SELECT	
	--model features
	f.HOME_GOALS_FOR_STRENGTH,
	f.HOME_GOALS_AGAINST_STRENGTH,
	f.HOME_XG_FOR_STRENGTH,
	f.HOME_XG_AGAINST_STRENGTH,
	f.HOME_SHOTS_FOR_STRENGTH,
	f.HOME_SHOTS_AGAINST_STRENGTH,
	f.HOME_SHOTS_ON_TARGET_FOR_STRENGTH,
	f.HOME_SHOTS_ON_TARGET_AGAINST_STRENGTH,
	f.HOME_PPDA_FOR_STRENGTH,
	f.HOME_PPDA_AGAINST_STRENGTH,
	f.HOME_DEEP_FOR_STRENGTH,
	f.HOME_DEEP_AGAINST_STRENGTH,
	f.AWAY_GOALS_FOR_STRENGTH,
	f.AWAY_GOALS_AGAINST_STRENGTH,
	f.AWAY_XG_FOR_STRENGTH,
	f.AWAY_XG_AGAINST_STRENGTH,
	f.AWAY_SHOTS_FOR_STRENGTH,
	f.AWAY_SHOTS_AGAINST_STRENGTH,
	f.AWAY_SHOTS_ON_TARGET_FOR_STRENGTH,
	f.AWAY_SHOTS_ON_TARGET_AGAINST_STRENGTH,
	f.AWAY_PPDA_FOR_STRENGTH,
	f.AWAY_PPDA_AGAINST_STRENGTH,
	f.AWAY_DEEP_FOR_STRENGTH,	
	f.AWAY_DEEP_AGAINST_STRENGTH,
	--target variables
	CASE WHEN s.HOME_GOALS > s.AWAY_GOALS THEN 1 ELSE 0 END home_win,
	CASE WHEN s.HOME_GOALS = s.AWAY_GOALS THEN 1 ELSE 0 END draw,
	CASE WHEN s.HOME_GOALS < s.AWAY_GOALS THEN 1 ELSE 0 END away_win
FROM 
	BETTING_DV.FOOTBALL_MATCH_HIS_B his
	JOIN BETTING_DV.FOOTBALL_MATCH_HIS_L_S_UNDERSTAT_TEAM_STRENGTH_FEATURES_EMA15_ha f
		ON his.FOOTBALL_MATCH_HIS_LID = f.FOOTBALL_MATCH_HIS_LID
	JOIN RAW_DV.FOOTBALL_MATCH_HIS_L_S_UNDERSTAT_TEAM_STATS s
		ON his.FOOTBALL_MATCH_HIS_LID = s.FOOTBALL_MATCH_HIS_LID
WHERE
	--features have to exist
	f.HOME_GOALS_FOR_STRENGTH IS NOT NULL AND
	f.HOME_SHOTS_FOR_STRENGTH IS NOT NULL and
	f.AWAY_GOALS_FOR_STRENGTH IS not NULL and
	f.AWAY_SHOTS_FOR_STRENGTH IS not NULL and
	--first season is skipped to have stable features
	his.SEASON <> '2014_2015';"""


#connect to db
dwh_conn = pyexasol.connect(dsn=dwh_host + ':' + dwh_port, user=dwh_user, password=dwh_pass)

pd_ft_imp = dwh_conn.export_to_pandas(v_sql)

dwh_conn.close()

pd_feature = pd_ft_imp.iloc[:,0:24]

pd_class = pd_ft_imp.iloc[:,24]


model=RandomForestClassifier()
model.fit(pd_feature,pd_class) 
feature_importances=pd.DataFrame({'features':pd_feature.columns,'feature_importance':model.feature_importances_})
feature_importances.sort_values('feature_importance',ascending=False)

# define models and parameters
model = XGBClassifier(objective ='multi:softprob')

case 
          when home_win = 1 then 'H'
          when draw = 1 then 'D'
          else 'A'
  end result

x = df_data.iloc[:,:24]
y = df_data.iloc[:,27:28]

#
# define hyperparameters
learning_rate = [0.05, 0.07,0.1]
n_estimators = [40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250]

max_depth = [2]
min_child_weight = [10]
subsample = [0.9]
colsample_bytree = [0.5]
gamma  = [0.2]

#
# define grid search
grid = dict(learning_rate=learning_rate, 
            max_depth=max_depth, 
            min_child_weight=min_child_weight, 
            gamma=gamma, 
            subsample=subsample,
            colsample_bytree=colsample_bytree,
            n_estimators = n_estimators            
           )

cv = RepeatedStratifiedKFold(n_splits=10, n_repeats=3, random_state=20)
grid_search = GridSearchCV(estimator=model, param_grid=grid, n_jobs=-1, cv=cv, scoring='accuracy',error_score=0)
grid_result = grid_search.fit(x, y)

def plot_grid_search(cv_results, grid_param_1, grid_param_2, name_param_1, name_param_2):
    # Get Test Scores Mean and std for each grid search
    scores_mean = cv_results['mean_test_score']
    scores_mean = np.array(scores_mean).reshape(len(grid_param_2),len(grid_param_1))

    scores_sd = cv_results['std_test_score']
    scores_sd = np.array(scores_sd).reshape(len(grid_param_2),len(grid_param_1))

    # Plot Grid search scores
    _, ax = plt.subplots(1,1)

    # Param1 is the X-axis, Param 2 is represented as a different curve (color line)
    for idx, val in enumerate(grid_param_2):
        ax.plot(grid_param_1, scores_mean[idx,:], '-o', label= name_param_2 + ': ' + str(val))

    ax.set_title("Grid Search Scores", fontsize=20, fontweight='bold')
    ax.set_xlabel(name_param_1, fontsize=16)
    ax.set_ylabel('CV Average Score', fontsize=16)
    ax.legend(loc="best", fontsize=15)
    ax.grid('on')

plot_grid_search(grid_result.cv_results_, n_estimators, learning_rate, 'n_estimators', 'learning_rate')

#get iterator month data
def get_iterator_data():

    #get iterator data
    v_sql = """SELECT distinct
        to_char(his.MATCH_DATE,'yyyymm') iterator
    FROM 
        BETTING_DV.FOOTBALL_MATCH_HIS_B his
        JOIN BETTING_DV.FOOTBALL_MATCH_HIS_L_S_UNDERSTAT_TEAM_STRENGTH_FEATURES_EMA15_ha f
            ON his.FOOTBALL_MATCH_HIS_LID = f.FOOTBALL_MATCH_HIS_LID
    WHERE
        --2014_15 season dropped for stable features
        season <> '2014_2015'
        --2015_16 is first training season
        AND season <> '2015_2016'
    ORDER BY 1;"""

    return dwh_conn.export_to_pandas(v_sql)

#get iterator data
df_iterator = get_iterator_data()

#
# combined models
#

#loop over month
for index, row in df_iterator.iterrows():
    print('Prediction for: ' + str(row['ITERATOR']))

def get_training_data_comb(month_filter):

    #load training data based on provided filter
    v_sql = """-- BETTING_MART.UNDERSTAT_TEAM_MODEL_FEATURES_EMA15 source
    SELECT	
        --model features
        f.HOME_GOALS_FOR_STRENGTH,
        f.HOME_GOALS_AGAINST_STRENGTH,
        f.HOME_XG_FOR_STRENGTH,
        f.HOME_XG_AGAINST_STRENGTH,
        f.HOME_SHOTS_FOR_STRENGTH,
        f.HOME_SHOTS_AGAINST_STRENGTH,
        f.HOME_SHOTS_ON_TARGET_FOR_STRENGTH,
        f.HOME_SHOTS_ON_TARGET_AGAINST_STRENGTH,
        f.HOME_PPDA_FOR_STRENGTH,
        f.HOME_PPDA_AGAINST_STRENGTH,
        f.HOME_DEEP_FOR_STRENGTH,
        f.HOME_DEEP_AGAINST_STRENGTH,
        f.AWAY_GOALS_FOR_STRENGTH,
        f.AWAY_GOALS_AGAINST_STRENGTH,
        f.AWAY_XG_FOR_STRENGTH,
        f.AWAY_XG_AGAINST_STRENGTH,
        f.AWAY_SHOTS_FOR_STRENGTH,
        f.AWAY_SHOTS_AGAINST_STRENGTH,
        f.AWAY_SHOTS_ON_TARGET_FOR_STRENGTH,
        f.AWAY_SHOTS_ON_TARGET_AGAINST_STRENGTH,
        f.AWAY_PPDA_FOR_STRENGTH,
        f.AWAY_PPDA_AGAINST_STRENGTH,
        f.AWAY_DEEP_FOR_STRENGTH,	
        f.AWAY_DEEP_AGAINST_STRENGTH,
        --prediction class
        case 
          when s.HOME_GOALS  > s.AWAY_GOALS then 'H'
          when s.HOME_GOALS = s.AWAY_GOALS then 'D'
          else 'A'
        end match_result
    FROM 
        BETTING_DV.FOOTBALL_MATCH_HIS_B his
        JOIN BETTING_DV.FOOTBALL_MATCH_HIS_L_S_UNDERSTAT_TEAM_STRENGTH_FEATURES_EMA15 f
            ON his.FOOTBALL_MATCH_HIS_LID = f.FOOTBALL_MATCH_HIS_LID
        JOIN RAW_DV.FOOTBALL_MATCH_HIS_L_S_UNDERSTAT_TEAM_STATS s
            ON his.FOOTBALL_MATCH_HIS_LID = s.FOOTBALL_MATCH_HIS_LID
    WHERE
        --features have to exist
        f.HOME_GOALS_FOR_STRENGTH IS NOT NULL AND
        f.HOME_SHOTS_FOR_STRENGTH IS NOT NULL and
        f.AWAY_GOALS_FOR_STRENGTH IS not NULL and
        f.AWAY_SHOTS_FOR_STRENGTH IS not NULL and
        --first season is skipped to have stable features
        his.SEASON <> '2014_2015' and
        --filter for iterator
        to_number(to_char(his.MATCH_DATE,'yyyymm')) < """ + str(month_filter) + ";"

    return dwh_conn.export_to_pandas(v_sql)

df_train_data = get_training_data_comb(row['ITERATOR'])
    
#split features and pred classes
df_train_x = df_train_data.iloc[:,:24]
df_train_y = df_train_data.iloc[:,24:25]
    
    
#
#train logistic regression model
#
print('...training logistic regression model')
    
solvers = 'liblinear'
penalty = 'l2'
c_values = 1.0
       
lr_model = LogisticRegression(solver=solvers, penalty=penalty, C=c_values, random_state=0).fit(df_train_x, df_train_y)

def get_pred_data_comb(month_filter):

    #load training data based on provided filter
    v_sql = """-- BETTING_MART.UNDERSTAT_TEAM_MODEL_FEATURES_EMA15 source
    SELECT
        f.football_match_his_lid,
        --model features
        f.HOME_GOALS_FOR_STRENGTH,
        f.HOME_GOALS_AGAINST_STRENGTH,
        f.HOME_XG_FOR_STRENGTH,
        f.HOME_XG_AGAINST_STRENGTH,
        f.HOME_SHOTS_FOR_STRENGTH,
        f.HOME_SHOTS_AGAINST_STRENGTH,
        f.HOME_SHOTS_ON_TARGET_FOR_STRENGTH,
        f.HOME_SHOTS_ON_TARGET_AGAINST_STRENGTH,
        f.HOME_PPDA_FOR_STRENGTH,
        f.HOME_PPDA_AGAINST_STRENGTH,
        f.HOME_DEEP_FOR_STRENGTH,
        f.HOME_DEEP_AGAINST_STRENGTH,
        f.AWAY_GOALS_FOR_STRENGTH,
        f.AWAY_GOALS_AGAINST_STRENGTH,
        f.AWAY_XG_FOR_STRENGTH,
        f.AWAY_XG_AGAINST_STRENGTH,
        f.AWAY_SHOTS_FOR_STRENGTH,
        f.AWAY_SHOTS_AGAINST_STRENGTH,
        f.AWAY_SHOTS_ON_TARGET_FOR_STRENGTH,
        f.AWAY_SHOTS_ON_TARGET_AGAINST_STRENGTH,
        f.AWAY_PPDA_FOR_STRENGTH,
        f.AWAY_PPDA_AGAINST_STRENGTH,
        f.AWAY_DEEP_FOR_STRENGTH,	
        f.AWAY_DEEP_AGAINST_STRENGTH
    FROM 
        BETTING_DV.FOOTBALL_MATCH_HIS_B his
        JOIN BETTING_DV.FOOTBALL_MATCH_HIS_L_S_UNDERSTAT_TEAM_STRENGTH_FEATURES_EMA15 f
            ON his.FOOTBALL_MATCH_HIS_LID = f.FOOTBALL_MATCH_HIS_LID
        JOIN RAW_DV.FOOTBALL_MATCH_HIS_L_S_UNDERSTAT_TEAM_STATS s
            ON his.FOOTBALL_MATCH_HIS_LID = s.FOOTBALL_MATCH_HIS_LID
    WHERE
        --features have to exist
        f.HOME_GOALS_FOR_STRENGTH IS NOT NULL AND
        f.HOME_SHOTS_FOR_STRENGTH IS NOT NULL and
        f.AWAY_GOALS_FOR_STRENGTH IS not NULL and
        f.AWAY_SHOTS_FOR_STRENGTH IS not NULL and
        --first season is skipped to have stable features
        his.SEASON <> '2014_2015' and
        --filter for iterator
        to_number(to_char(his.MATCH_DATE,'yyyymm')) = """ + str(month_filter) + ";"

    return dwh_conn.export_to_pandas(v_sql)

#
# predicting logistic regression
#
    
#get data for prediction
df_pred_data = get_pred_data_comb(row['ITERATOR'])
    
#do prediction
df_pred_x = df_pred_data.iloc[:,1:25]
    
print('...executing logistic regression prediction')  
    
df_pred_y = lr_model.predict_proba(df_pred_x)
    
#create result data frame
df_result_data = pd.DataFrame(df_pred_data.iloc[:,0:1])
df_result_data['model'] = 'Logistic Regression EMA15 combined'
df_result_data['home_win_prob'] = df_pred_y[:,2:3]
df_result_data['draw_prob'] = df_pred_y[:,1:2]
df_result_data['away_win_prob'] = df_pred_y[:,0:1]
df_result_data['interator_param'] = str(row['ITERATOR'])
    
print('......data written to the db')    
dwh_conn.import_from_pandas(df_result_data,('BETTING_DV','football_match_his_l_understat_pred_ema15'))