Premier League Table Ranking III - Problem

Given a SeasonStats table containing Premier League season statistics for teams, write a solution to calculate the points, goal difference, and position for each team in each season.

Ranking Rules:

  • Teams are first ranked by their total points (highest to lowest)
  • If points are tied, teams are ranked by goal difference (highest to lowest)
  • If goal difference is also tied, teams are ranked alphabetically by team name

Points Calculation:

  • 3 points for a win
  • 1 point for a draw
  • 0 points for a loss

Goal Difference: goals_for - goals_against

Return the result table ordered by season_id ascending, then by position ascending, and finally by team_name ascending.

Table Schema

SeasonStats
Column Name Type Description
season_id PK int Season identifier
team_id PK int Team identifier
team_name varchar Team name
matches_played int Number of matches played
wins int Number of wins
draws int Number of draws
losses int Number of losses
goals_for int Goals scored by the team
goals_against int Goals conceded by the team
Primary Key: (season_id, team_id)
Note: Each row represents one team's statistics for a specific season

Input & Output

Example 1 — Premier League 2021-2022 Seasons
Input Table:
season_id team_id team_name matches_played wins draws losses goals_for goals_against
2021 1 Manchester City 38 29 6 3 99 26
2021 2 Liverpool 38 28 8 2 94 26
2021 3 Chelsea 38 21 11 6 76 33
2022 1 Manchester City 38 28 5 5 94 33
2022 2 Arsenal 38 26 6 6 88 43
Output:
season_id team_id team_name points goal_difference position
2021 1 Manchester City 93 73 1
2021 2 Liverpool 92 68 2
2021 3 Chelsea 74 43 3
2022 1 Manchester City 89 61 1
2022 2 Arsenal 84 45 2
💡 Note:

For 2021 season: Manchester City has 93 points (29×3 + 6×1) and goal difference of 73 (99-26), ranking 1st. Liverpool has 92 points (28×3 + 8×1) and goal difference of 68 (94-26), ranking 2nd. For 2022 season: Manchester City leads with 89 points and +61 goal difference, followed by Arsenal with 84 points and +45 goal difference.
Example 2 — Teams with Same Points
Input Table:
season_id team_id team_name matches_played wins draws losses goals_for goals_against
2023 1 Arsenal 10 6 3 1 25 15
2023 2 Chelsea 10 7 0 3 20 12
2023 3 Brighton 10 7 0 3 18 10
Output:
season_id team_id team_name points goal_difference position
2023 1 Arsenal 21 10 1
2023 3 Brighton 21 8 2
2023 2 Chelsea 21 8 3
💡 Note:

All three teams have 21 points, so ranking goes by goal difference. Arsenal (+10) ranks 1st. Brighton and Chelsea both have +8 goal difference, so they are ranked alphabetically: Brighton (2nd), Chelsea (3rd).

Constraints

  • 1 ≤ season_id ≤ 2030
  • 1 ≤ team_id ≤ 100
  • 1 ≤ team_name.length ≤ 50
  • matches_played = wins + draws + losses
  • 0 ≤ wins, draws, losses ≤ 50
  • 0 ≤ goals_for, goals_against ≤ 200

Visualization

Tap to expand
Premier League Table Ranking III INPUT Season Home Away H-A Score 2023 Arsenal Chelsea 3-1 2023 Chelsea ManUtd 2-2 2023 ManUtd Arsenal 0-2 Points System Win = 3 pts Draw = 1 pt Loss = 0 pts Goal Difference GD = Goals_For - Goals_Against Ranking Priority 1. Points (highest first) 2. Goal Difference (highest) 3. Alphabetical (A-Z) ALGORITHM STEPS 1 Group by Season Partition matches by season_id 2 Calculate Stats Sum points, GF, GA per team Team Stats (Season 2023): Arsenal: W=2 GF=5 GA=1 GD=+4 P=6 Chelsea: W=0 D=1 GF=3 GA=5 GD=-2 P=1 ManUtd: W=0 D=1 GF=2 GA=4 GD=-2 P=1 3 Sort Teams ORDER BY pts DESC, GD DESC, name Compare: pts1 vs pts2 If equal: GD1 vs GD2 If equal: name1 vs name2 (A-Z) 4 Assign Positions ROW_NUMBER() for each season FINAL RESULT Pos Team Pts GD Season 1 Arsenal 6 +4 2023 2 Chelsea 1 -2 2023 3 ManUtd 1 -2 2023 Tiebreaker Applied! Chelsea vs ManUtd: Same pts (1) Same GD (-2) --> Chelsea < ManUtd -- SQL Solution WITH stats AS ( SELECT team, season, SUM(pts) as points, SUM(gf)-SUM(ga) as gd ) SELECT ROW_NUMBER() OVER( PARTITION BY season ORDER BY pts DESC, gd DESC, name) Key Insight: Use window functions (ROW_NUMBER) with PARTITION BY season to rank teams independently per season. The ORDER BY clause handles multi-level sorting: points DESC, goal_difference DESC, team_name ASC. Aggregate match results using CASE WHEN to calculate wins/draws/losses and goal stats per team. TutorialsPoint - Premier League Table Ranking III | Optimal Solution

Related Problems

Asked in
Meta 12 Amazon 8 Microsoft 6
23.4K Views
Medium Frequency
~18 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen