Premier league table ranking iii

# Table: SeasonStats
# 
# +------------------+---------+
# | Column Name      | Type    |
# +------------------+---------+
# | season_id        | int     |
# | team_id          | int     |
# | team_name        | varchar |
# | matches_played   | int     |
# | wins             | int     |
# | draws            | int     |
# | losses           | int     |
# | goals_for        | int     |
# | goals_against    | int     |
# +------------------+---------+
# (season_id, team_id) is the unique key for this table.
# This table contains season id, team id, team name, matches played, wins, draws, losses, goals scored (goals_for), and goals conceded (goals_against) for each team in each season.
# 
# Write a solution to calculate the points, goal difference, and position for each team in each season. The position ranking should be determined as follows:
# 
# Teams are first ranked by their total points (highest to lowest)
# 
# If points are tied, teams are then ranked by their goal difference (highest to lowest)
# 
# If goal difference is also tied, teams are then ranked alphabetically by team name
# 
# Points are calculated as follows:
# 
# 3 points for a win
# 
# 1 point for a draw
# 
# 0 points for a loss
# 
# Goal difference is calculated as: goals_for - goals_against
# 
# Return the result table ordered by season_id in ascending order, then by position in ascending order, and finally by team_name in ascending order.
# 
# The query result format is in the following example.
#
# Example 1:
# +------------+---------+-------------------+----------------+------+-------+--------+-----------+---------------+
# | 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            |
# | 2021       | 4       | Tottenham         | 38             | 22   | 5     | 11     | 69        | 40            |
# | 2021       | 5       | Arsenal           | 38             | 22   | 3     | 13     | 61        | 48            |
# | 2022       | 1       | Manchester City   | 38             | 28   | 5     | 5      | 94        | 33            |
# | 2022       | 2       | Arsenal           | 38             | 26   | 6     | 6      | 88        | 43            |
# | 2022       | 3       | Manchester United | 38             | 23   | 6     | 9      | 58        | 43            |
# | 2022       | 4       | Newcastle         | 38             | 19   | 14    | 5      | 68        | 33            |
# | 2022       | 5       | Liverpool         | 38             | 19   | 10    | 9      | 75        | 47            |
# +------------+---------+-------------------+----------------+------+-------+--------+-----------+---------------+

import pandas as pd

def process_team_standings(season_stats: pd.DataFrame) -> pd.DataFrame:
  season_stats['points'] = season_stats['wins'] * 3 + season_stats['draws']
  season_stats['goal_difference'] = season_stats['goals_for'] - season_stats['goals_against']
  season_stats = season_stats.sort_values(['season_id', 'points', 'goal_difference', 'team_name'], ascending=[True, False, False, True])
  season_stats['position'] = season_stats.groupby('season_id').cumcount() + 1
  return season_stats[['season_id', 'team_id', 'team_name', 'points', 'goal_difference', 'position']]