# 3246. Premier League Table Ranking ðŸ”’

## Description

Table: TeamStats

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


Output:

+--+--++-+
\| 2       \| Liverpool       \| 20     \| 1        \|
\| 1       \| Manchester City \| 20     \| 1        \|
\| 3       \| Chelsea         \| 18     \| 3        \|
\| 4       \| Arsenal         \| 16     \| 4        \|
\| 5       \| Tottenham       \| 14     \| 5        \|
+--+--+----+


Explanation:

• Manchester City and Liverpool both have 20 points (6 wins * 3 points + 2 draws * 1 point), so they share position 1.
• Chelsea has 18 points (5 wins * 3 points + 3 draws * 1 point) and is position 3rd.
• Arsenal has 16 points (4 wins * 3 points + 4 draws * 1 point) and is position 4th.
• Tottenham has 14 points (3 wins * 3 points + 5 draws * 1 point) and is position 5th.

The output table is ordered by points in descending order, then by team_name in ascending order.

</div>

## Solutions

### Solution 1: Window Function

We can use the RANK() window function to calculate the ranking of the teams, and then sort by score and team name.

• import pandas as pd

def calculate_team_standings(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)
team_stats = team_stats.sort_values(
by=["points", "team_name"], ascending=[False, True]
)
return team_stats[["team_id", "team_name", "points", "position"]]


• # Write your MySQL query statement below
SELECT
team_id,
team_name,
wins * 3 + draws points,
RANK() OVER (ORDER BY (wins * 3 + draws) DESC) position
FROM TeamStats
ORDER BY 3 DESC, 2;