Premier League Table Ranking III - Practice Coding Problems

Premier League Table Ranking III - Problem

Database Medium

🏆 Premier League Table Ranking III

Welcome to the world of football analytics! You're tasked with creating a complete Premier League table ranking system that mimics how professional football leagues calculate standings.

The Challenge: Given season statistics for multiple teams across different seasons, calculate each team's points, goal difference, and final league position using official football ranking rules.

📊 Table Structure

The SeasonStats table contains:

season_id: The season identifier
team_id: Unique team identifier
team_name: Team name
matches_played, wins, draws, losses: Match statistics
goals_for, goals_against: Goals scored and conceded

🎯 Ranking Rules

Points Calculation: 3 points for a win, 1 point for a draw, 0 points for a loss
Goal Difference: goals_for - goals_against
Position Ranking Priority:
- 1st: Total points (highest to lowest)
- 2nd: Goal difference (highest to lowest)
- 3rd: Team name (alphabetically)

Output: Return results ordered by season_id (ascending), then position (ascending), then team_name (ascending).

Input & Output

example_1.py — Basic Season Ranking

$ Input: SeasonStats: season_id=2021, 5 teams with different points and goal differences

› Output: Manchester City (93 pts, +73 GD, position 1), Liverpool (92 pts, +68 GD, position 2), Chelsea (74 pts, +43 GD, position 3), Tottenham (71 pts, +29 GD, position 4), Arsenal (69 pts, +13 GD, position 5)

💡 Note: Teams are ranked by points first (Manchester City leads with 93), then by goal difference when points are equal, and finally alphabetically by name if both points and goal difference are tied.

example_2.py — Multiple Seasons

$ Input: SeasonStats: season_id=2021 and 2022, each with 5 teams

› Output: Results show separate rankings for each season, with Manchester City winning both seasons but different runners-up (Liverpool in 2021, Arsenal in 2022)

💡 Note: Each season is ranked independently. The PARTITION BY season_id ensures teams are only compared within their own season.

example_3.py — Tie-Breaking Scenario

$ Input: Teams with identical points but different goal differences, or identical points and goal differences but different names

› Output: Demonstrates how tie-breaking works: points → goal difference → alphabetical team name

💡 Note: When multiple criteria are tied, the next criterion in the hierarchy determines the ranking, ensuring consistent and fair positioning.

Constraints

1 ≤ season_id ≤ 10⁴
1 ≤ team_id ≤ 10³
1 ≤ team_name.length ≤ 50
0 ≤ wins, draws, losses ≤ 50
matches_played = wins + draws + losses
0 ≤ goals_for, goals_against ≤ 200

Visualization

Tap to expand

Understanding the Visualization

Calculate Scores

Each win = 3 points, each draw = 1 point, each loss = 0 points

Calculate Bonus

Goal difference = Goals scored minus goals conceded

Rank Students

Sort by total points, then bonus points, then name

Separate by Year

Each academic year (season) has its own ranking

Key Takeaway

🎯 Key Insight: Window functions like RANK() handle complex multi-criteria sorting efficiently in a single database operation, making them perfect for ranking systems like sports tables.

Asked in

E ESPN 45 DK DraftKings 38 FD FanDuel 32 S Sky Sports 28

The optimal solution uses SQL window functions, specifically RANK() OVER (PARTITION BY season_id ORDER BY points DESC, goal_difference DESC, team_name ASC). This approach calculates points as (wins × 3 + draws × 1), goal difference as (goals_for - goals_against), and efficiently ranks teams within each season using multi-criteria sorting in a single pass. The PARTITION BY ensures teams are ranked separately by season, while the ORDER BY clause handles the complex tie-breaking rules automatically.

Common Approaches

Approach	Time	Space	Notes
✓ Basic Calculation with Manual Ranking	O(n²)	O(1)	Calculate points and goal difference, then use subqueries for ranking
Window Function Ranking (Optimal)	O(n log n)	O(1)	Use SQL window functions for efficient ranking in a single query

Basic Calculation with Manual Ranking — Algorithm Steps

Calculate points as (wins × 3) + (draws × 1)
Calculate goal difference as goals_for - goals_against
Use correlated subquery to count teams with better ranking criteria
Add 1 to the count to get the position
Order results by season, position, and team name

Visualization

Tap to expand

Step-by-Step Walkthrough

Calculate Base Stats

For each team, calculate points and goal difference

Compare with Others

Count how many teams have better ranking criteria

Determine Position

Position = count of better teams + 1

Code -

solution.c — C

// SQL Solution using correlated subqueries
char* query = "\
SELECT \
    season_id, \
    team_id, \
    team_name, \
    (wins * 3 + draws * 1) AS points, \
    (goals_for - goals_against) AS goal_difference, \
    ( \
        SELECT COUNT(*) + 1 \
        FROM SeasonStats s2 \
        WHERE s2.season_id = s1.season_id \
        AND ( \
            (s2.wins * 3 + s2.draws * 1) > (s1.wins * 3 + s1.draws * 1) \
            OR ( \
                (s2.wins * 3 + s2.draws * 1) = (s1.wins * 3 + s1.draws * 1) \
                AND (s2.goals_for - s2.goals_against) > (s1.goals_for - s1.goals_against) \
            ) \
            OR ( \
                (s2.wins * 3 + s2.draws * 1) = (s1.wins * 3 + s1.draws * 1) \
                AND (s2.goals_for - s2.goals_against) = (s1.goals_for - s1.goals_against) \
                AND s2.team_name < s1.team_name \
            ) \
        ) \
    ) AS position \
FROM SeasonStats s1 \
ORDER BY season_id ASC, position ASC, team_name ASC;";

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Each row requires a subquery that scans all other rows in the same season

⚠ Quadratic Growth

Space Complexity

O(1)

No additional space beyond result set

✓ Linear Space

34.2K Views

Medium Frequency

~12 min Avg. Time

1.5K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

🏆 Premier League Table Ranking III

📊 Table Structure

🎯 Ranking Rules

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Basic Calculation with Manual Ranking — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler