Welcome to Subscribe On Youtube
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;