Watch the video solution for Premier League Table Ranking III, a medium level problem involving Database. This walkthrough by Everyday Data Science has 492 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: SeasonStats
+------------------+---------+ | Column Name | Type | +------------------+---------+ | season_id | int | | team_id | int | | team_name | varchar | | matches_played | int | | wins | int | | draws | int | | losses | int | | goals_for | int | | goals_against | int | +------------------+---------+ (season_id, team_id) is the unique key for this table. This table contains season id, team id, team name, matches played, wins, draws, losses, goals scored (goals_for), and goals conceded (goals_against) for each team in each season.
Write a solution to calculate the points, goal difference, and position for each team in each season. The position ranking should be determined as follows:
Points are calculated as follows:
3 points for a win1 point for a draw0 points for a lossGoal difference is calculated as: goals_for - goals_against
Return the result table ordered by season_id in ascending order, then by position in ascending order, and finally by team_name in ascending order.
The query result format is in the following example.
Example:
Input:
SeasonStats table:
+------------+---------+-------------------+----------------+------+-------+--------+-----------+---------------+ | season_id | team_id | team_name | matches_played | wins | draws | losses | goals_for | goals_against | +------------+---------+-------------------+----------------+------+-------+--------+-----------+---------------+ | 2021 | 1 | Manchester City | 38 | 29 | 6 | 3 | 99 | 26 | | 2021 | 2 | Liverpool | 38 | 28 | 8 | 2 | 94 | 26 | | 2021 | 3 | Chelsea | 38 | 21 | 11 | 6 | 76 | 33 | | 2021 | 4 | Tottenham | 38 | 22 | 5 | 11 | 69 | 40 | | 2021 | 5 | Arsenal | 38 | 22 | 3 | 13 | 61 | 48 | | 2022 | 1 | Manchester City | 38 | 28 | 5 | 5 | 94 | 33 | | 2022 | 2 | Arsenal | 38 | 26 | 6 | 6 | 88 | 43 | | 2022 | 3 | Manchester United | 38 | 23 | 6 | 9 | 58 | 43 | | 2022 | 4 | Newcastle | 38 | 19 | 14 | 5 | 68 | 33 | | 2022 | 5 | Liverpool | 38 | 19 | 10 | 9 | 75 | 47 | +------------+---------+-------------------+----------------+------+-------+--------+-----------+---------------+
Output:
+------------+---------+-------------------+--------+-----------------+----------+ | season_id | team_id | team_name | points | goal_difference | position | +------------+---------+-------------------+--------+-----------------+----------+ | 2021 | 1 | Manchester City | 93 | 73 | 1 | | 2021 | 2 | Liverpool | 92 | 68 | 2 | | 2021 | 3 | Chelsea | 74 | 43 | 3 | | 2021 | 4 | Tottenham | 71 | 29 | 4 | | 2021 | 5 | Arsenal | 69 | 13 | 5 | | 2022 | 1 | Manchester City | 89 | 61 | 1 | | 2022 | 2 | Arsenal | 84 | 45 | 2 | | 2022 | 3 | Manchester United | 75 | 15 | 3 | | 2022 | 4 | Newcastle | 71 | 35 | 4 | | 2022 | 5 | Liverpool | 67 | 28 | 5 | +------------+---------+-------------------+--------+-----------------+----------+
Explanation:
Problem Overview: You are given Premier League team statistics and need to produce the league table ranking. Teams must be ordered by their performance metrics (such as points and tie‑breakers) and assigned a rank following league table rules.
Approach 1: Window Function Ranking (O(n log n) time, O(n) space)
The most practical solution uses SQL window functions. First sort teams by the ranking criteria used in league tables, typically points DESC, then tie‑breakers such as goal_difference DESC and goals_scored DESC. After ordering the rows, apply a ranking function like RANK() or DENSE_RANK() using OVER (ORDER BY ...). The database engine performs the ordering and assigns ranks in a single pass over the sorted result set. Time complexity is O(n log n) due to sorting, and space complexity is O(n) for the intermediate ordered dataset.
This approach is ideal for relational datasets because ranking logic stays inside the query. Window functions are optimized by most SQL engines and avoid complicated self‑joins or subqueries. If you work with analytics queries or leaderboard problems, this pattern appears frequently. See related patterns in database queries and SQL window functions.
Approach 2: Pandas Window Ranking (O(n log n) time, O(n) space)
In a Pandas environment, replicate the same logic using DataFrame sorting followed by ranking. First call sort_values() on the ranking columns in descending order. Then use rank() with the appropriate method (such as dense or min) to generate league positions. Pandas internally sorts the rows and computes ranks across the ordered dataset. Time complexity remains O(n log n) because sorting dominates, and space complexity is O(n) for the DataFrame.
This version is useful when the dataset is already loaded into Python for analysis. It mirrors SQL logic closely, which makes it easy to translate between query solutions and data analysis workflows. More patterns like this appear in pandas data manipulation problems.
Recommended for interviews: The window function solution is the expected answer. Interviewers want to see that you know how to compute rankings using RANK() or DENSE_RANK() instead of manual comparisons or nested queries. Understanding how ordering and ranking interact shows strong SQL fundamentals.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL Window Function Ranking | O(n log n) | O(n) | Best choice for SQL interview questions and leaderboard-style ranking queries |
| Pandas Sort + Rank | O(n log n) | O(n) | When working with the dataset in Python for analytics or preprocessing |