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 ProcessRaw Season DatateamwinsdrawsGFGACity2969926Liverpool2889426CalculatePoints = W×3 + D×1Goal Diff = GF - GARank by:1. Points DESC2. Goal Diff DESC3. Team Name ASCFinal League TableteampointsGDposCity93731Liverpool92682⚽ Manchester City wins the title with 93 points!
Understanding the Visualization
1
Input
Raw season statistics for each team
2
Calculate
Compute points and goal difference
3
Rank
Apply multi-criteria ranking within seasons
Key Takeaway
🎯 Key Insight: Use RANK() OVER with PARTITION BY season_id for complex multi-criteria ranking within groups

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