Table: TeamStats
+------------------+---------+ | Column Name | Type | +------------------+---------+ | team_id | int | | team_name | varchar | | matches_played | int | | wins | int | | draws | int | | losses | int | +------------------+---------+ team_id is the unique key for this table. This table contains team id, team name, matches_played, wins, draws, and losses.
Write a solution to calculate the points, position, and tier for each team in the league. Points are calculated as follows:
3 points for a win1 point for a draw0 points for a lossNote: Teams with the same points must be assigned the same position.
Tier ranking:
3 tiers based on points:33% of teams33% of teams 34% of teamsReturn the result table ordered by points in descending, and then by team_name in ascending order.
The query result format is in the following example.
Example:
Input:
TeamStats table:
+---------+-------------------+----------------+------+-------+--------+ | team_id | team_name | matches_played | wins | draws | losses | +---------+-------------------+----------------+------+-------+--------+ | 1 | Chelsea | 22 | 13 | 2 | 7 | | 2 | Nottingham Forest | 27 | 6 | 6 | 15 | | 3 | Liverpool | 17 | 1 | 8 | 8 | | 4 | Aston Villa | 20 | 1 | 6 | 13 | | 5 | Fulham | 31 | 18 | 1 | 12 | | 6 | Burnley | 26 | 6 | 9 | 11 | | 7 | Newcastle United | 33 | 11 | 10 | 12 | | 8 | Sheffield United | 20 | 18 | 2 | 0 | | 9 | Luton Town | 5 | 4 | 0 | 1 | | 10 | Everton | 14 | 2 | 6 | 6 | +---------+-------------------+----------------+------+-------+--------+
Output:
+-------------------+--------+----------+---------+ | team_name | points | position | tier | +-------------------+--------+----------+---------+ | Sheffield United | 56 | 1 | Tier 1 | | Fulham | 55 | 2 | Tier 1 | | Newcastle United | 43 | 3 | Tier 1 | | Chelsea | 41 | 4 | Tier 1 | | Burnley | 27 | 5 | Tier 2 | | Nottingham Forest | 24 | 6 | Tier 2 | | Everton | 12 | 7 | Tier 2 | | Luton Town | 12 | 7 | Tier 2 | | Liverpool | 11 | 9 | Tier 3 | | Aston Villa | 9 | 10 | Tier 3 | +-------------------+--------+----------+---------+
Explanation:
Tier Calculation:
Problem Overview: You need to generate the Premier League standings table from match or team statistics and assign a ranking based on competition rules. Teams are ordered primarily by points, with tie‑breakers such as goal difference and goals scored. The result should return teams in their correct league position.
Approach 1: Aggregation + Window Function with CASE WHEN (O(n log n) time, O(n) space)
The clean solution uses SQL window functions to compute rankings after calculating the metrics used for ordering. First aggregate team statistics such as total points, goal difference, and goals scored. If points are derived from match results, use CASE WHEN to assign 3 points for a win, 1 for a draw, and 0 for a loss, then sum them per team.
Once the totals are available, order teams using the official tie‑break hierarchy. This typically means sorting by points DESC, then goal_difference DESC, and finally goals_scored DESC. A window ranking function such as RANK() or DENSE_RANK() assigns the league position based on that ordering. Window functions are ideal here because they compute ranks across the full dataset without collapsing rows like a GROUP BY result.
The key insight is separating metric calculation from ranking. CASE WHEN handles the conditional scoring logic, while the window function handles ordering and position assignment. This pattern appears frequently in SQL ranking problems and leaderboard systems.
In Pandas, the same logic translates directly: compute points with conditional operations, sort by the tie‑break columns, and assign ranks using rank() with the appropriate method.
Recommended for interviews: The window function solution is what interviewers expect for SQL ranking problems. It demonstrates strong understanding of database queries, conditional aggregation, and window functions. Brute force manual ranking logic would be far more complex and unnecessary when SQL provides ranking primitives.
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.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Aggregation + CASE WHEN + Window Rank | O(n log n) | O(n) | Best general solution for leaderboard or ranking problems with multiple tie-break rules |
| Manual Sorting Without Window Functions | O(n log n) | O(n) | Older SQL engines without window function support |
Leetcode MEDIUM 3252 - Premier League Table Ranking 2 WINDOW - Explained by Everyday Data Science • Everyday Data Science • 598 views views
Practice Premier League Table Ranking II with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor