# 3252. Premier League Table Ranking II ðŸ”’

## Description

Table: TeamStats

+++
\| team_id          \| int     \|
\| team_name        \| varchar \|
\| matches_played   \| int     \|
\| wins             \| int     \|
\| draws            \| int     \|
\| losses           \| int     \|
+++-++-++-+-+-+-+---+


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.

</div>

## Solutions

### Solution 1: Window Function + CASE WHEN

We can use the window function RANK() to calculate each teamâ€™s points, ranking, and the total number of teams. Then, we can use the CASE WHEN statement to determine the grade of each team.

• 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)
)
total_teams = len(team_stats)
team_stats["tier"] = np.where(
team_stats["position"] <= np.ceil(total_teams / 3.0),
"Tier 1",
np.where(
team_stats["position"] <= np.ceil(2 * total_teams / 3.0), "Tier 2", "Tier 3"
),
)
team_stats = team_stats.sort_values(
by=["points", "team_name"], ascending=[False, True]
)
return team_stats[["team_name", "points", "position", "tier"]]


• WITH
T AS (
SELECT
team_name,
wins * 3 + draws AS points,
RANK() OVER (ORDER BY wins * 3 + draws DESC) AS position,
COUNT(1) OVER () AS total_teams
FROM TeamStats
)
SELECT
team_name,
points,
position,
CASE
WHEN position <= CEIL(total_teams / 3.0) THEN 'Tier 1'
WHEN position <= CEIL(2 * total_teams / 3.0) THEN 'Tier 2'
ELSE 'Tier 3'
END tier
FROM T
ORDER BY 2 DESC, 1;