The Premier League's Legacy of Footballing Excellence: Part 1
Photo by Chaos Soccer Gear on Unsplash
Since its birth in 1992, the Premier League has captivated the world with its fast-paced action, fierce rivalries, and unforgettable narratives. This analysis takes us on a journey through three decades of English football, exploring the evolution of teams, and how that has shaped the Premier League into the global phenomenon it is today.
The league boasts dramatic upsets, unexpected triumphs, and gut-wrenching defeats, woven together to create one of the most competitive and thrilling in the world. From the dominance of Manchester United and Arsenal, to the rise of new contenders like Manchester City, and the occasional underdog fairytale, each season brings a fresh wave of excitement and surprises.
We delve into the data – match results, team performances, goal-scoring trends – to uncover hidden insights and patterns. By examining the shifting tides of success and the impact of managerial changes, we aim to gain a deeper understanding of the Premier League.
About the Premier League
The Premier League, the top division of the English League, was founded on 20th February 1992 following the decision of clubs in the Football League First Division to break away from the Football League, founded in 1888.
About dataset
The dataset was retrieved from Kaggle. It comprises 12026 matches from the 1992/93 season to the end of the 2022/23 season. The dataset comprises of the following variables:
- season
- match_date
- home: home team
- away: away team
- home_goal: goals scored by home team
- away_goal: goals scored by away team
- ftr: full time result
- H: Home win
- A: Away win
- D: Draw
Highest number of goals in a match
The highest-scoring match occured in 2007/2008 season between Portsmouth and Reading, where a staggering total of 11 goals were scored with Portsmouth emerging victorious with a commanding 7-4 win over Reading.
The second highest goals scored in match was 10 with Manchester United, Tottenham and Arsenal involved in all the matches. The most memorable was a 5-5 draw between West Brom and Manchester United in 2012/13 season, as well as a pounding of Arsenal by Manchester United by 8 goals to 2 in the 2011/12 season.
SELECT
season,
home,
home_goal,
away_goal,
away,
(home_goal + away_goal) AS total_goals, -- Sum of goals from home and away teams
RANK() OVER(ORDER BY (home_goal + away_goal) DESC) AS goals_rank -- Match ranking based on all goals scored
FROM
'pl_results.csv'
ORDER BY
goals_rank
LIMIT 20;Highest goal difference in a match
Four matches stand out in the English Premier League football history for the highest goal difference, each with the winning team scoring 9 goals to nil. Manchester United achieved this feat twice at home, defeating Southampton and Ipswich Town in the 2020/21 and 1994/95 seasons, respectively. Southampton suffered another humiliating loss at home against Leicester City in the 2019/20 season. The most recent was that of Liverpool showcasing their dominance by thrashing Bournemouth at home in the 2022/23 season.
SELECT
season,
home,
home_goal,
away_goal,
away,
ABS(home_goal - away_goal) AS goal_difference, -- Absolute goal difference
RANK()
over(ORDER BY ABS(home_goal - away_goal) DESC) AS goals_rank
FROM 'pl_results.csv'
ORDER BY goals_rank
LIMIT 10; Most goals scored by home team
- The record for the highest number of goals scored at home remains at 9, with Tottenham and Liverpool scoring against Wigan Athletic and Bournemouth respectively.
- Manchester United repeated this score twice, triumphing over Ipswich Town and Southampton.
SELECT season,
home,
home_goal,
away_goal,
away
FROM 'pl_results.csv'
ORDER BY home_goal DESC,
season
LIMIT 15; Most goals scored by away team
- The highest number of goals scored at an away ground stands at 9, with Leicester City dominating Southampton with a remarkable 9-0 victory during the 2019/20 season.
- Manchester United also left a mark when they defeated Nottingham Forest 8-1 away during the 1998/99 season.
- Three other teams have achieved notable victories away from home, scoring 7 goals in a single match. Tottenham, Liverpool, and Nottingham Forest all achieved this scoring against Hull City, Crystal Palace, and Sheffield Wednesday respectively.
SELECT season,
home,
home_goal,
away_goal,
away
FROM 'pl_results.csv'
ORDER BY away_goal DESC
LIMIT 20; Most goals scored by a team in a season
- Chelsea was the first team to cross the 100-mark when they scored 103 goals in the 2009/10 season. Only Manchester City scored more when they broke the record in the 2017/18 season by scoring 106 goals. City did cross the 100-mark on two other separate occasions, scoring 102 goals in both the 2013/14 and the 2019/20 seasons.
- Manchester United dominated goal-scoring in the 90s with the most goals scored between the 1995/96 to 2001/02 season. Arsenal dominated between 2002 up to 2005 when they won the league with an unbeaten run.
- After 2010, Manchester City has dominated goal-scoring with more goals than any team in 11 out of 12 seasons.
- Blackburn, relegated in 2011/12, were once the highest-scoring team in the 1992/93 as well as the 1994/95 season.
-- Calculate home goals scored in each season
WITH home_goals AS (
SELECT season,
home,
home_goal
FROM 'pl_results.csv'
),
-- Calculate away goals scored in each season
away_goals AS (
SELECT season,
away,
away_goal
FROM 'pl_results.csv'
),
-- Combine home and away goals data
season_goals_tb AS (
SELECT season,
home AS team,
home_goal AS goals
FROM home_goals
UNION ALL
SELECT season,
away,
away_goal
FROM away_goals
)
-- Select the season, team, and total goals scored in each season for the top-scoring team
SELECT season,
team,
season_goals
FROM (
-- Calculate the total goals scored by each team in each season and rank them
SELECT season,
team,
SUM(goals) AS season_goals,
RANK() OVER (PARTITION BY season ORDER BY SUM(goals) DESC) AS team_rank -- Compute rank from total season goals
FROM season_goals_tb
GROUP BY season,
team
ORDER BY season,
season_goals DESC
) AS goals_rank
WHERE team_rank = 1 -- Filter for only the top scoring teams only each season
ORDER BY season_goals DESC;So did chelsea win the league after being the first team to cross the 100 mark goals by scoring 103 goals?
- Indeed chelsea did go and win the 2009/10 premier league title with 86 points scoring 103 goals and conceding 32. Despite scoring so many goals, they only managed to beat the second placed team, Manchester United, with just one point (85 points).
- Arsenal and Tottenham came third and fourth respectively.
- Burnley, Hull City and Portsmouth were relegated to the Championship.
-- Calculate points, wins, draws, and losses for home matches
WITH home_points AS
(SELECT home as team,
home_goal as scored,
away_goal as conceded,
(CASE
-- 3 points for win, 1 point for draw, 0 points for loss for home team
WHEN home_goal > away_goal THEN 3
WHEN home_goal = away_goal THEN 1
ELSE 0 END) AS points,
-- Track all home wins, draws and losses
(CASE WHEN ftr = 'H' THEN 1 END) AS win,
(CASE WHEN ftr = 'D' THEN 1 END) AS draw,
(CASE WHEN ftr = 'A' THEN 1 END) AS loss
FROM 'pl_results.csv'
WHERE season = '2009/2010'), -- filter for the 2009/10 season
-- Calculate points, wins, draws, and losses for away matches
away_points AS
(SELECT away as team,
away_goal,
home_goal,
(CASE
-- 3 points for win, 1 point for draw, 0 points for loss for away team
WHEN home_goal < away_goal THEN 3
WHEN home_goal = away_goal THEN 1
ELSE 0 END) AS points,
-- Track all away wins, draws and losses
(CASE WHEN ftr = 'A' THEN 1 END) AS win,
(CASE WHEN ftr = 'D' THEN 1 END) AS draw,
(CASE WHEN ftr = 'H' THEN 1 END) AS loss
FROM 'pl_results.csv'
WHERE season = '2009/2010'),
-- Making the 2009/10 Premier League table
premier_league_table AS
(SELECT
DENSE_RANK() OVER(ORDER BY SUM(points) DESC,
SUM(scored - conceded) DESC) AS team_position, -- Team position based on points and goals scored/conceded
team,
COUNT(team) AS matches,
SUM(win) AS win,
SUM(draw) AS draw,
SUM(loss) AS loss,
SUM(scored) AS goal_scored,
SUM( conceded) AS goal_conceded,
SUM(scored - conceded) AS goal_diff, -- goal difference
SUM(points) AS points
FROM
(SELECT *
FROM home_points
UNION ALL -- Append away matches to home matches
SELECT *
FROM away_points) AS points_table
GROUP BY team)
-- Retrieve and order the Premier League table for the 2009/2010 season
SELECT *
FROM premier_league_table
ORDER BY team_position;Which season has the highest total number of goals scored
- The highest scoring season was 1992/93 with 1222 goals, while the lowest was 2006/07 with 931 goals.
- The average goals per match has fluctuated between 2.45 and 2.85, with the highest in the most recent season (2022/23) and the lowest in 2006/07.
- Early Premier League seasons (1992-1995) saw more goals due to a higher number of matches (462 compared to the current 380). This changed in the 1995/96 season when the league went from 22 to 20 teams.
SELECT season,
count(home) AS total_matches,
sum(home_goal + away_goal) AS total_goals, -- Total goals scored each season
round(avg(home_goal + away_goal), 2) AS avg_goals -- Average goals scored in a match each season
FROM 'pl_results.csv'
GROUP BY season
ORDER BY avg_goals DESC;