Remaining Fixtures

[Move Klose 303]

3 sides I'd probably be keen to avoid are Cardiff, Bristol C & Barnsley. 

Luckily we only have 1 of those left and hopefully last day we wont need to be worrying about that outcome.

[Crabbycanary3 994]

6 hours ago, Moosebadge said:

As a watford fan we have hit form. Since new boss came in we have not conceded first in 12 consecutive games. And we are now unbeaten away in our last 5 away games. Winning 3 and drawing 2. Its 2015 all over again, we didnt enter an automatic place until 3 games to go. We are 4 points ahead of that season and recently look like a proper team. We finished on 89 points in 2015.

I saw a good chunk of the Blackburn game. A tough place to go and win, no doubt but what I saw was not impressive and better teams (which you have many to play still) than Blackburn would have beaten you on Wed.  I do acknowledge your form (home form is the best in the League) and also that a snapshot of a game is not a fair basis to form an opinion but you said you had seen a massive improvement. You scored 3 goals but in 15 previous away games you'd scored 8. Birmingham have the same away record as yourselves. You have the toughest of run ins (especially away)compared with the rest of us, so you are going to have to go balls out to change that and keep it up for 14 (or 13, see below) games. 

5 hours ago, Moosebadge said:

We beat you, drew with Brentford and lost to Swansea previously. I think we may lose tomorrow. I am not to bothered if we do, the aim has to be to win 10 of last 14, we won 8 and drew 3 of our last 14 games so short of this by 3 points. But have seen a massive improvement in last 4 games. I see it as a shoot out between us and Brentford for 2nd. I dont think you will wobble although we pulled back the same amount of points on Bournemouth in 2015. Although still finished 2nd.

So, with that in mind, you'll need to win 10 of the last 13. If you do that, you'll definitely deserve automatic. Which 3 games are you thinking you will lose? The 2015 season has no bearing on this season, either your team, our team, nor Bournemouth from then

[Feedthewolf 4,844]

On 25/02/2021 at 15:36, Petriix said:

I've done a fair bit of analysis. Without going into the boring details of my algorithm, I project we will finish on 94 pts. That will be enough to win the league by about 6 pts. We've already played our hardest fixtures and other have not. We only need 8 more wins to be fairly certain of the championship, and definitely automatic promotion. The ultimate outcome is more dependent on our performances against the bottom-half teams. We don't actually need to beat any of our immediate rivals, although avoiding defeat would be ideal.

OI!

Get your algorithm out, you bloody tease.

[Petriix 2,834]

18 hours ago, Feedthewolf said:

OI!

Get your algorithm out, you bloody tease.

Ok, you asked for it...
Firstly I scraped some data from the https://www.footballwebpages.co.uk/championship API then imported it into an SQL database which just shows the teamID, dateTime of the fixtures and the home/away score with NULL for games not yet completed. Then I queried the data to split the results into home and away against top, middle and bottom teams and projected average future results based on the average points against that segment of teams; then I adjusted it for both overall and recent form to help adjust for outliers.

Here's the query:

Quote

WITH ct as (SELECT RANK() OVER(order by points desc, gd desc) as position, teamID, name, points, gd, played from (select t.teamid, t.name, (select 3 * count(*) from games where homeTeamID = t.teamID and homeGoals > awayGoals and homeGoals is not null) + (select 3 * count(*) from games where awayTeamID = t.teamID and homeGoals < awayGoals and homeGoals is not null) + (select count(*) from games where (homeTeamID = t.teamID or awayTeamID = t.teamID) and homeGoals = awayGoals) as points, (select sum(homeGoals) - sum(awayGoals) from games where homeTeamID = t.teamID) + (select sum(awayGoals) - sum(homeGoals) from games where awayTeamID = t.teamID) as GD, (select count(*) from games where (homeTeamID = t.teamID or awayTeamID = t.teamID) and homeGoals is not null) as played from teams t)tbl),
ctCalc as (
    select teamID, position, name, points, gd,
    ((select 3 * count(*) from games inner join ct ct1 on ct1.position < 9 and ct1.teamID = games.awayTeamID where homeTeamID = ct.teamID and homeGoals > awayGoals and homeGoals is not null) + (select count(*) from games inner join ct ct1 on ct1.position < 9 and ct1.teamID = games.awayTeamID where homeTeamID = ct.teamID and homeGoals = awayGoals and homeGoals is not null)) as [pts-vs-top-home], (select count(*) from games inner join ct ct1 on ct1.position < 9 and ct1.teamID = games.awayTeamID where homeTeamID = ct.teamID and homeGoals is not null) as [games-vs-top-home], (select count(*) from games inner join ct ct1 on ct1.position < 9 and ct1.teamID = games.awayTeamID where homeTeamID = ct.teamID and homeGoals is null) as [remaining-games-vs-top-home],
    ((select 3 * count(*) from games inner join ct ct1 on ct1.position > 8 and ct1.position < 17 and ct1.teamID = games.awayTeamID where homeTeamID = ct.teamID and homeGoals > awayGoals and homeGoals is not null) + (select count(*) from games inner join ct ct1 on ct1.position > 8 and ct1.position < 17 and ct1.teamID = games.awayTeamID where homeTeamID = ct.teamID and homeGoals = awayGoals and homeGoals is not null)) as [pts-vs-mid-home], (select count(*) from games inner join ct ct1 on ct1.position > 8 and ct1.position < 17 and ct1.teamID = games.awayTeamID where homeTeamID = ct.teamID and homeGoals is not null) as [games-vs-mid-home], (select count(*) from games inner join ct ct1 on ct1.position > 8 and ct1.position < 17 and ct1.teamID = games.awayTeamID where homeTeamID = ct.teamID and homeGoals is null) as [remaining-games-vs-mid-home],
    ((select 3 * count(*) from games inner join ct ct1 on ct1.position > 16 and ct1.teamID = games.awayTeamID where homeTeamID = ct.teamID and homeGoals > awayGoals and homeGoals is not null) + (select count(*) from games inner join ct ct1 on ct1.position > 16 and ct1.teamID = games.awayTeamID where homeTeamID = ct.teamID and homeGoals = awayGoals and homeGoals is not null)) as [pts-vs-bot-home], (select count(*) from games inner join ct ct1 on ct1.position > 16 and ct1.teamID = games.awayTeamID where homeTeamID = ct.teamID and homeGoals is not null) as [games-vs-bot-home], (select count(*) from games inner join ct ct1 on ct1.position > 16 and ct1.teamID = games.awayTeamID where homeTeamID = ct.teamID and homeGoals is null) as [remaining-games-vs-bot-home],

    ((select 3 * count(*) from games inner join ct ct1 on ct1.position < 9 and ct1.teamID = games.homeTeamID where awayTeamID = ct.teamID and awayGoals > homeGoals and homeGoals is not null) + (select count(*) from games inner join ct ct1 on ct1.position < 9 and ct1.teamID = games.homeTeamID where awayTeamID = ct.teamID and homeGoals = awayGoals and homeGoals is not null)) as [pts-vs-top-away], (select count(*) from games inner join ct ct1 on ct1.position < 9 and ct1.teamID = games.homeTeamID where awayTeamID = ct.teamID and homeGoals is not null) as [games-vs-top-away], (select count(*) from games inner join ct ct1 on ct1.position < 9 and ct1.teamID = games.homeTeamID where awayTeamID = ct.teamID and homeGoals is null) as [remaining-games-vs-top-away],
    ((select 3 * count(*) from games inner join ct ct1 on ct1.position > 8 and ct1.position < 17 and ct1.teamID = games.homeTeamID where awayTeamID = ct.teamID and awayGoals > homeGoals and homeGoals is not null) + (select count(*) from games inner join ct ct1 on ct1.position > 8 and ct1.position < 17 and ct1.teamID = games.homeTeamID where awayTeamID = ct.teamID and homeGoals = awayGoals and homeGoals is not null)) as [pts-vs-mid-away], (select count(*) from games inner join ct ct1 on ct1.position > 8 and ct1.position < 17 and ct1.teamID = games.homeTeamID where awayTeamID = ct.teamID and homeGoals is not null) as [games-vs-mid-away], (select count(*) from games inner join ct ct1 on ct1.position > 8 and ct1.position < 17 and ct1.teamID = games.homeTeamID where awayTeamID = ct.teamID and homeGoals is null) as [remaining-games-vs-mid-away],
    ((select 3 * count(*) from games inner join ct ct1 on ct1.position > 16 and ct1.teamID = games.homeTeamID where awayTeamID = ct.teamID and awayGoals > homeGoals and homeGoals is not null) + (select count(*) from games inner join ct ct1 on ct1.position > 16 and ct1.teamID = games.homeTeamID where awayTeamID = ct.teamID and homeGoals = awayGoals and homeGoals is not null)) as [pts-vs-bot-away], (select count(*) from games inner join ct ct1 on ct1.position > 16 and ct1.teamID = games.homeTeamID where awayTeamID = ct.teamID and homeGoals is not null) as [games-vs-bot-away], (select count(*) from games inner join ct ct1 on ct1.position > 16 and ct1.teamID = games.homeTeamID where awayTeamID = ct.teamID and homeGoals is null) as [remaining-games-vs-bot-away]
    , (select cast( ((select 3 * count(*) from (select top(10) * from games where homeGoals is not null and ( homeTeamID = ct.teamID or awayTeamID = ct.teamID) order by date desc) as gm1 where (homeTeamID = ct.teamID and homeGoals > awayGoals) or (awayTeamID = ct.teamID and awayGoals > homeGoals)) + (select count(*) from (select top(10) * from games where homeGoals is not null and ( homeTeamID = ct.teamID or awayTeamID = ct.teamID) order by date desc) as gm2 where (awayTeamID = ct.teamID or homeTeamID = ct.teamID) and homeGoals = awayGoals)) as decimal) / 10) as [recent-form], cast (points as decimal) / cast (played as decimal) as [season-form]
    from ct
),
ctAll as (
select teamID, name, position as [current-position], points as [current-points], GD,
CAST(ROUND((
    case when [games-vs-top-home] = 0 then ([season-form]) else ((CAST([pts-vs-top-home] as decimal)/CAST([games-vs-top-home] as decimal)) * 2 + ([season-form])) / 3 end * CAST([remaining-games-vs-top-home] as decimal) +
    case when [games-vs-mid-home] = 0 then ([season-form]) else ((CAST([pts-vs-mid-home] as decimal)/CAST([games-vs-mid-home] as decimal)) * 2 + ([season-form])) / 3 end * CAST([remaining-games-vs-mid-home] as decimal) +
    case when [games-vs-bot-home] = 0 then ([season-form]) else ((CAST([pts-vs-bot-home] as decimal)/CAST([games-vs-bot-home] as decimal)) * 2 + ([season-form])) / 3 end * CAST([remaining-games-vs-bot-home] as decimal) +
    case when [games-vs-top-away] = 0 then ([season-form]) else ((CAST([pts-vs-top-away] as decimal)/CAST([games-vs-top-away] as decimal)) * 2 + ([season-form])) / 3 end * CAST([remaining-games-vs-top-away] as decimal) +
    case when [games-vs-mid-away] = 0 then ([season-form]) else ((CAST([pts-vs-mid-away] as decimal)/CAST([games-vs-mid-away] as decimal)) * 2 + ([season-form])) / 3 end * CAST([remaining-games-vs-mid-away] as decimal) +
    case when [games-vs-bot-away] = 0 then ([season-form]) else ((CAST([pts-vs-bot-away] as decimal)/CAST([games-vs-bot-away] as decimal)) * 2 + ([season-form])) / 3 end * CAST([remaining-games-vs-bot-away] as decimal
))
+ points, 18) as decimal) as [projected-points],
CAST(ROUND((
    case when [games-vs-top-home] = 0 then ([season-form]) else ((CAST([pts-vs-top-home] as decimal)/CAST([games-vs-top-home] as decimal)) * 2 + ([season-form])) / 3  end * CAST([remaining-games-vs-top-home] as decimal) +
    case when [games-vs-mid-home] = 0 then ([season-form]) else ((CAST([pts-vs-mid-home] as decimal)/CAST([games-vs-mid-home] as decimal)) * 2 + ([season-form])) / 3  end * CAST([remaining-games-vs-mid-home] as decimal) +
    case when [games-vs-bot-home] = 0 then ([season-form]) else ((CAST([pts-vs-bot-home] as decimal)/CAST([games-vs-bot-home] as decimal)) * 2 + ([season-form])) / 3  end * CAST([remaining-games-vs-bot-home] as decimal) +
    case when [games-vs-top-away] = 0 then ([season-form]) else ((CAST([pts-vs-top-away] as decimal)/CAST([games-vs-top-away] as decimal)) * 2 + ([season-form])) / 3 end * CAST([remaining-games-vs-top-away] as decimal) +
    case when [games-vs-mid-away] = 0 then ([season-form]) else ((CAST([pts-vs-mid-away] as decimal)/CAST([games-vs-mid-away] as decimal)) * 2 + ([season-form])) / 3 end * CAST([remaining-games-vs-mid-away] as decimal) +
    case when [games-vs-bot-away] = 0 then ([season-form]) else ((CAST([pts-vs-bot-away] as decimal)/CAST([games-vs-bot-away] as decimal)) * 2 + ([season-form])) / 3 end * CAST([remaining-games-vs-bot-away] as decimal
))
* (([recent-form] / [season-form] -1) / 2 + 1)
+ points, 18) as decimal) as [form-adjusted]
, [season-form]
, (([recent-form] / [season-form] -1) / 2 + 1) as [form-percentage]
,case when [games-vs-top-home] = 0 then ([season-form]) else ((CAST([pts-vs-top-home] as decimal)/CAST([games-vs-top-home] as decimal)) * 2 + ([season-form])) / 3 end as HT
,case when [games-vs-mid-home] = 0 then ([season-form]) else ((CAST([pts-vs-mid-home] as decimal)/CAST([games-vs-mid-home] as decimal)) * 2 + ([season-form])) / 3 end as HM
,case when [games-vs-bot-home] = 0 then ([season-form]) else ((CAST([pts-vs-bot-home] as decimal)/CAST([games-vs-bot-home] as decimal)) * 2 + ([season-form])) / 3 end as HB
,case when [games-vs-top-away] = 0 then ([season-form]) else ((CAST([pts-vs-top-away] as decimal)/CAST([games-vs-top-away] as decimal)) * 2 + ([season-form])) / 3 end as AT
,case when [games-vs-mid-away] = 0 then ([season-form]) else ((CAST([pts-vs-mid-away] as decimal)/CAST([games-vs-mid-away] as decimal)) * 2 + ([season-form])) / 3 end as AM
,case when [games-vs-bot-away] = 0 then ([season-form]) else ((CAST([pts-vs-bot-away] as decimal)/CAST([games-vs-bot-away] as decimal)) * 2 + ([season-form])) / 3 end as AB
from ctCalc
)
SELECT * from ctAll order by [form-adjusted] desc, gd desc

and here's the results as of before today's fixtures:

Quote

teamID	name	current-position	current-points	GD	projected-points	form-adjusted	season-form	form-percentage	HT	HM	HB	AT	AM	AB
16	Norwich City	1	67	20	100	101	2.09375	1.049253	2.697917	1.936012	2.142361	1.475694	2.43125	2.297917
90	Swansea City	4	59	19	90	93	1.966667	1.084745	1.988889	2.655556	1.877778	1.822222	1.988889	1.588889
49	Brentford	3	60	25	86	86	1.875	1.006666	1.691667	1.691667	2.402778	2.091667	1.558333	1.736111
41	Watford	2	61	20	84	86	1.848485	1.068032	1.682828	2.282828	2.616162	0.782828	1.854257	1.616162
45	Barnsley	8	48	1	75	76	1.548387	1.048958	1.049462	2.293907	1.682796	0.849462	1.182796	2.516129
23	Cardiff City	7	49	14	71	74	1.53125	1.153061	0.954861	1.177083	2.110417	1.677083	1.84375	1.44375
36	Reading	5	51	7	73	72	1.59375	0.939216	1.73125	1.197917	2.13125	1.197917	1.769345	1.464583
47	AFC Bournemouth	6	50	14	70	65	1.515152	0.764	1.671717	1.727273	1.743146	1.171717	1.338384	1.393939
35	Queens Park Rangers	13	40	-4	60	65	1.333333	1.25	1.555556	1.333333	1.333333	1.333333	0.888889	1.333333
31	Millwall	11	43	3	61	63	1.34375	1.132558	1.114583	1.114583	1.400298	1.247917	1.247917	1.78125
14	Middlesbrough	9	46	3	66	63	1.4375	0.847826	1.368056	1.479167	1.8125	1.279167	1.368056	1.279167
39	Stoke City	10	45	2	66	63	1.40625	0.855556	0.94494	1.635417	2.202083	0.86875	1.46875	1.516369
26	Derby County	17	38	-8	55	60	1.1875	1.3	1.173611	1.157738	1.229167	1.229167	1.329167	1.0625
32	Nottingham Forest	13	40	-4	55	59	1.212121	1.2425	0.737374	1.40404	1.737374	0.40404	1.292929	1.626263
50	Bristol City	12	42	-11	60	58	1.3125	0.842857	0.659722	1.4375	2.104167	0.818452	2.0375	0.9375
4	Blackburn Rovers	16	39	8	60	57	1.258065	0.897436	0.952688	1.308244	2.086022	0.552688	1.086022	1.752688
34	Preston North End	15	40	-8	58	54	1.25	0.82	0.583333	1.083333	1.35	1.369048	1.75	1.416667
57	Luton Town	18	38	-11	55	54	1.225806	0.948684	1.075269	1.265745	1.741935	0.980031	0.408602	1.875269
62	Sheffield Wednesday	20	34	-14	50	53	1.096774	1.183823	1.032258	1.565591	1.565591	1.298925	0.476703	0.765591
37	Rotherham United	23	29	-8	46	49	0.966667	1.172413	0.433333	1.255556	1.488889	0.322222	1.1	1.488889
24	Coventry City	20	34	-14	49	49	1.0625	1.017647	1.401786	1.020833	1.554167	0.544643	0.754167	1.1875
55	Huddersfield Town	19	36	-9	52	47	1.125	0.722222	1.375	1.458333	1.541667	0.708333	0.875	0.819444
3	Birmingham City	22	31	-17	43	42	0.96875	0.912903	0.722917	0.489583	0.799107	0.85625	1.465774	1.322917
91	Wycombe Wanderers	24	23	-28	34	36	0.741935	1.241304	1.313978	0.380645	0.802867	0.247312	0.513978	1.180645

 

[Feedthewolf 4,844]

Safe to say a lot of that went way over my head, but that final table is really interesting.

What are the column values towards the right-hand side of the bottom table?

HT

HM

HB

AT

AM

AB

 

Edit: ignore me, just worked it out... home and away against top, middle and bottom segments.

Edited February 27, 2021 by Feedthewolf

[Feedthewolf 4,844]

The headline figure for my money is the HT value for Norwich... 2.697917! Considering we still have most of the top teams to play at home, that augurs well. Surprised the figure is that high given that home advantage has been largely negated in the absence of fans.

[Petriix 2,834]

8 minutes ago, Feedthewolf said:

The headline figure for my money is the HT value for Norwich... 2.697917! Considering we still have most of the top teams to play at home, that augurs well. Surprised the figure is that high given that home advantage has been largely negated in the absence of fans.

That's the adjusted figure. We've won all our home games against current top 8 teams (Swansea, Cardiff and Barnsley).

[ricardo 7,346]

On 26/02/2021 at 10:54, Moosebadge said:

 We finished on 89 points in 2015.

Won't be enough this season.

[Petriix 2,834]

We're 7 pts ahead of where we were after 32 games two years ago. With the same return from our final 14 we'd finish on 101 pts. I would hope that 90 would be enough but it's possible that Brentford and Swansea could go on long winning runs. Watford can now only reach 99 pts maximum.

[Feedthewolf 4,844]

16 minutes ago, Petriix said:

We're 7 pts ahead of where we were after 32 games two years ago. With the same return from our final 14 we'd finish on 101 pts. I would hope that 90 would be enough but it's possible that Brentford and Swansea could go on long winning runs. Watford can now only reach 99 pts maximum.

Swansea will do well to get a run of wins going in the next few weeks. They play twice a week right up to the international break and have some really tough fixtures: Bristol City (H), Stoke (A), Middlesbrough (H), Blackburn (A), Luton (A), Bournemouth (A), Cardiff (H).

They have a better-looking run-in, but they could be out of striking distance by then.

[FenwayFrank 2,456]

Stoke score 😁

[Crabbycanary3 994]

Brentford only 2 clean sheets in their last 16 games. If they go up, it won't matter how good Toney is