1

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)

3
  • Hi Dan, the position needs to be per week or for all the weeks combined? The points = 3 for Win, 1 for Tie and 0 for loss right? In a new table or as columns to the existing table? Oct 14, 2019 at 14:01
  • Hello @RandiVertongen, indeed, the Position needs to be per Gameweek but not for all the weeks combined, but only for gameweeks < current gameweek. The points = 3 for Win, 1 for Tie and 0 for loss right? => correct. In a new table or as columns to the existing table? => doesn't really matter, in the end the i will need to have a table similar to the current one, but for each row(match) to have current position in standings and number of points just before the match happened
    – Dan
    Oct 14, 2019 at 14:04
  • anyone with this? Trying to build a data model to predict Over/Under result and for this i need to know the form of each team at a certain moment in time, pre each game
    – Dan
    Nov 5, 2019 at 8:20

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.