I have the following data in a sql database and i need that for each game in the table to add new columns with the Rank in Table, Number of Points and Goals Scored at home, for each Gameweek (the trickiest part). Basically what i need is that after each gameweek (order ascending) to create a standings table and then bring Kpis from the standings table to each team for each match in the gamesweek.
The goal of all this is to build a Data Model with KPIs on how the team performed in the standings before each game and try to make some predictions afterwards ps: if anyone interested, after the model will be ready please get in touch and will gladly share
Here is the table
Div DataMeciului HomeTeam AwayTeam FTHG FTAG Gameweek
E0 2018-08-10 Man United Leicester 2 1 1
E0 2018-08-11 Bournemouth Cardiff 2 0 1
E0 2018-08-11 Fulham Crystal Palace0 2 1
E0 2018-08-11 Huddersfield Chelsea 0 3 1
E0 2018-08-11 Newcastle Tottenham 1 2 1
E0 2018-08-11 Watford Brighton. 2 0 1
E0 2018-08-11 Wolves Everton 2 2 1
E0 2018-08-12 Arsenal Man City 0 2 1
E0 2018-08-12 Liverpool West Ham 4 0 1
E0 2018-08-12 Southampton Burnley 0 0 1
E0 2018-08-18 Cardiff Newcastle 0 0 2
E0 2018-08-18 Chelsea Arsenal 3 2 2
E0 2018-08-18 Everton Southampton 2 1 2
E0 2018-08-18 Leicester Wolves 2 0 2
E0 2018-08-18 Tottenham Fulham 3 1 2
E0 2018-08-18 West Ham Bournemouth 1 2 2
E0 2018-08-19 Brighton Man United 3 2 2
E0 2018-08-19 Burnley Watford 1 3 2
E0 2018-08-19 Man City Huddersfield 6 1 2
E0 2018-08-20 Crystal Palace Liverpool 0 2 2
E0 2018-08-25 Arsenal West Ham 3 1 3
E0 2018-08-25 Bournemouth Everton 2 2 3
E0 2018-08-25 Huddersfield Cardiff 0 0 3
E0 2018-08-25 Liverpool Brighton 1 0 3
E0 2018-08-25 Southampton Leicester 1 2 3
E0 2018-08-25 Wolves Man City 1 1 3
E0 2018-08-26 Fulham Burnley 4 2 3
E0 2018-08-26 Newcastle Chelsea 1 2 3
E0 2018-08-26 Watford Crystal Palace 2 1 3
E0 2018-08-27 Man United Tottenham 0 3 3
E0 2018-09-01 Brighton Fulham 2 2 4
E0 2018-09-01 Chelsea Bournemouth 2 0 4
E0 2018-09-01 Crystal Palace Southampton 0 2 4
E0 2018-09-01 Everton Huddersfield 1 1 4
E0 2018-09-01 Leicester Liverpool 1 2 4
E0 2018-09-01 Man City Newcastle 2 1 4
E0 2018-09-01 West Ham Wolves 0 1 4
E0 2018-09-02 Burnley Man United 0 2 4
E0 2018-09-02 Cardiff Arsenal 2 3 4
E0 2018-09-02 Watford Tottenham 2 1 4
Desired outcome
HTPts HTPos HomeTeam AwayTeam ATPos ATPts
3 1 Man United Leicester 15 0
3 3 Bournemouth Cardiff 16 0
0 13 Fulham Crystal Palace 6 3
Sorting the temptables to define position should be done one order by PTS, SUM(FTHG - FTAG)
