Welcome to Subscribe On Youtube

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;

All Problems

All Solutions