Premier League Table Ranking II - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase6 min read

Problem Statement

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 win
1 point for a draw
0 points for a loss

Note: Teams with the same points must be assigned the same position.

Tier ranking:

Divide the league into 3 tiers based on points:
Tier 1: Top 33% of teams
Tier 2: Middle 33% of teams
Tier 3: Bottom 34% of teams
In case of ties at tier boundaries, place tied teams in the higher tier.

Return 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:

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.

Approach Overview

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.

Solution

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.

Code

MySQL Pandas

MySQL

Pandas

Try this approach in the editor →

Detailed Complexity Analysis

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

Video Solution

Leetcode MEDIUM 3252 - Premier League Table Ranking 2 WINDOW - Explained by Everyday Data Science • Everyday Data Science • 626 views views

Frequently Asked Questions

Is Premier League Table Ranking II easy or hard?

Premier League Table Ranking II is typically classified as Medium difficulty. The challenge comes from correctly applying SQL window functions and implementing the tie‑break ranking logic used in league standings.

Premier League Table Ranking II Python/Java solution

For SQL versions such as MySQL, use CASE WHEN to compute match points and a window function like RANK() for ordering. In Python with Pandas, compute points using conditional columns, sort the DataFrame by tie‑break fields, and assign rankings using the rank() function.

How to solve Premier League Table Ranking II in O(n)?

Pure O(n) ranking is generally not achievable in SQL because leaderboard generation requires sorting by tie‑break columns. The closest practical solution uses aggregation plus a window ranking function, which runs in O(n log n) due to sorting.

What is the best approach for Premier League Table Ranking II?

The best approach uses SQL window functions combined with CASE WHEN logic. First compute team points using conditional expressions, then sort teams by points, goal difference, and goals scored. Apply a window ranking function such as RANK() or DENSE_RANK() to generate the league position.

Is Premier League Table Ranking II asked at Google/Amazon/Meta?

Database ranking and leaderboard queries appear frequently in SQL interview rounds at companies like Amazon, Meta, and fintech or analytics teams. Problems involving window functions, ranking, and conditional aggregation test practical SQL skills used in reporting pipelines.

What data structure is used in Premier League Table Ranking II?

The solution relies on relational database operations rather than traditional data structures. Key features include SQL window functions for ranking and conditional aggregation using CASE WHEN expressions.

What is the time complexity of Premier League Table Ranking II?

The query runs in O(n log n) time because the database must sort the teams by ranking criteria such as points and goal difference. Aggregation and CASE WHEN calculations are linear operations, while the ORDER BY used in the window function introduces the sorting cost.

Ready to solve this problem?

Practice Premier League Table Ranking II with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyMedium

Acceptance58.5%

Approaches1

Reading time6 min

Practice this problem

Open in Editor