Premier league table ranking ii

# Table: TeamStats
# 
# +------------------+---------+
# | Column Name      | Type    |
# +------------------+---------+
# | team_id          | int     |
# | team_name        | varchar |
# | matches_played   | int     |
# | wins             | int     |
# | draws            | int     |
# | losses           | int     |
# +------------------+---------+
# team_id is the unique key for this table.
# This table contains team id, team name, matches_played, wins, draws, and losses.
# 
# Write a solution to calculate the points, position, and tier for each team in the league. Points are calculated as follows:
# 
# 3 points for a win
# 
# 1 point for a draw
# 
# 0 points for a loss
# 
# Note: Teams with the same points must be assigned the same position.
# 
# Tier ranking:
# 
# Divide the league into 3 tiers based on points:
# 
# Tier 1: Top 33% of teams
# 
# Tier 2: Middle 33% of teams
# 
# Tier 3: Bottom 34% of teams
# 
# In case of ties at tier boundaries, place tied teams in the higher tier.
# 
# Return the result table ordered by points in descending, and then by team_name in ascending order.
# 
# The query result format is in the following example.
#
# Example 1:
# Input:
# TeamStats table:
# +---------+-------------------+----------------+------+-------+--------+
# | team_id | team_name         | matches_played | wins | draws | losses |
# +---------+-------------------+----------------+------+-------+--------+
# | 1       | Chelsea           | 22             | 13   | 2     | 7      |
# | 2       | Nottingham Forest | 27             | 6    | 6     | 15     |
# | 3       | Liverpool         | 17             | 1    | 8     | 8      |
# | 4       | Aston Villa       | 20             | 1    | 6     | 13     |
# | 5       | Fulham            | 31             | 18   | 1     | 12     |
# | 6       | Burnley           | 26             | 6    | 9     | 11     |
# | 7       | Newcastle United  | 33             | 11   | 10    | 12     |
# | 8       | Sheffield United  | 20             | 18   | 2     | 0      |
# | 9       | Luton Town        | 5              | 4    | 0     | 1      |
# | 10      | Everton           | 14             | 2    | 6     | 6      |
# +---------+-------------------+----------------+------+-------+--------+
# Output:
# +-------------------+--------+----------+---------+
# | team_name         | points | position | tier    |
# +-------------------+--------+----------+---------+
# | Sheffield United  | 56     | 1        | Tier 1  |
# | Fulham            | 55     | 2        | Tier 1  |
# | Newcastle United  | 43     | 3        | Tier 1  |
# | Chelsea           | 41     | 4        | Tier 1  |
# | Burnley           | 27     | 5        | Tier 2  |
# | Nottingham Forest | 24     | 6        | Tier 2  |
# | Everton           | 12     | 7        | Tier 2  |
# | Luton Town        | 12     | 7        | Tier 2  |
# | Liverpool         | 11     | 9        | Tier 3  |
# | Aston Villa       | 9      | 10       | Tier 3  |
# +-------------------+--------+----------+---------+
# Explanation:
# Sheffield United has 56 points (18 wins * 3 points + 2 draws * 1 point) and is in position 1.
# Fulham has 55 points (18 wins * 3 points + 1 draw * 1 point) and is in position 2.
# Newcastle United has 43 points (11 wins * 3 points + 10 draws * 1 point) and is in position 3.
# Chelsea has 41 points (13 wins * 3 points + 2 draws * 1 point) and is in position 4.
# Burnley has 27 points (6 wins * 3 points + 9 draws * 1 point) and is in position 5.
# Nottingham Forest has 24 points (6 wins * 3 points + 6 draws * 1 point) and is in position 6.
# Everton and Luton Town both have 12 points, with Everton having 2 wins * 3 points + 6 draws * 1 point, and Luton Town having 4 wins * 3 points. Both teams share position 7.
# Liverpool has 11 points (1 win * 3 points + 8 draws * 1 point) and is in position 9.
# Aston Villa has 9 points (1 win * 3 points + 6 draws * 1 point) and is in position 10.
# Tier Calculation:
# Tier 1: The top 33% of teams based on points. Sheffield United, Fulham, Newcastle United, and Chelsea fall into Tier 1.
# Tier 2: The middle 33% of teams. Burnley, Nottingham Forest, Everton, and Luton Town fall into Tier 2.
# Tier 3: The bottom 34% of teams. Liverpool and Aston Villa fall into Tier 3.

import math
import pandas as pd

def calculate_team_tiers(team_stats: pd.DataFrame) -> pd.DataFrame:
  team_stats['points'] = team_stats['wins'] * 3 + team_stats['draws']
  team_stats['position'] = team_stats['points'].rank(method='min', ascending=False).astype(int)
  n = len(team_stats)
  tier1_cutoff = math.ceil(n / 3)
  tier2_cutoff = math.ceil(2 * n / 3)

  def get_tier(pos):
    if pos <= tier1_cutoff:
      return 'Tier 1'
    elif pos <= tier2_cutoff:
      return 'Tier 2'
    else:
      return 'Tier 3'

  team_stats['tier'] = team_stats['position'].apply(get_tier)
  return team_stats[['team_name', 'points', 'position', 'tier']].sort_values(['points', 'team_name'], ascending=[False, True])