Premier League Table Ranking II

Problem

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 attier boundaries , place tied teams in the higher tier.

Return the result table ordered by points in descending ,** ** and then by team_name inascending order.

The query result format is in the following example.

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45


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.

Solution

Intuition

We need to compute points and assign positions (ranks) for each team, then divide teams into 3 tiers based on points. Ties at tier boundaries must be placed in the higher tier. SQL window functions and careful tier assignment logic are required. In pandas, we can use rank and quantile logic.

Approach

Compute points as (wins * 3 + draws * 1).
Assign position using RANK() OVER (ORDER BY points DESC, team_name ASC).
Sort by points DESC, team_name ASC. Assign tiers:
- Tier 1: Top 33% (rounded up, including ties at boundary)
- Tier 2: Next 33% (rounded up, including ties at boundary)
- Tier 3: Remaining (bottom 34%)
- If a tie occurs at a boundary, all teams with that points value are placed in the higher tier.

Code

MySQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26


WITH Ranked AS (
  SELECT team_name, (wins * 3 + draws) AS points,
         RANK() OVER (ORDER BY (wins * 3 + draws) DESC, team_name ASC) AS position
  FROM TeamStats
),
Ordered AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY points DESC, team_name ASC) AS rn
  FROM Ranked
),
Counts AS (
  SELECT COUNT(*) AS total FROM TeamStats
),
Tiers AS (
  SELECT o.*, c.total,
         CEIL(c.total * 0.33) AS t1,
         CEIL(c.total * 0.66) AS t2
  FROM Ordered o CROSS JOIN Counts c
)
SELECT team_name, points, position,
       CASE
         WHEN rn <= t1 THEN 'Tier 1'
         WHEN rn <= t2 THEN 'Tier 2'
         ELSE 'Tier 3'
       END AS tier
FROM Tiers
ORDER BY points DESC, team_name ASC;

PostgreSQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26


WITH Ranked AS (
  SELECT team_name, (wins * 3 + draws) AS points,
         RANK() OVER (ORDER BY (wins * 3 + draws) DESC, team_name ASC) AS position
  FROM TeamStats
),
Ordered AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY points DESC, team_name ASC) AS rn
  FROM Ranked
),
Counts AS (
  SELECT COUNT(*) AS total FROM TeamStats
),
Tiers AS (
  SELECT o.*, c.total,
         CEIL(c.total * 0.33) AS t1,
         CEIL(c.total * 0.66) AS t2
  FROM Ordered o CROSS JOIN Counts c
)
SELECT team_name, points, position,
       CASE
         WHEN rn <= t1 THEN 'Tier 1'
         WHEN rn <= t2 THEN 'Tier 2'
         ELSE 'Tier 3'
       END AS tier
FROM Tiers
ORDER BY points DESC, team_name ASC;

Python (pandas)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


import pandas as pd
import numpy as np

# Assume df is the TeamStats DataFrame
def premier_league_ranking_ii(df: pd.DataFrame) -> pd.DataFrame:
    df = df.copy()
    df['points'] = df['wins'] * 3 + df['draws']
    df = df.sort_values(['points', 'team_name'], ascending=[False, True])
    df['position'] = df[['points', 'team_name']].rank(method='min', ascending=[False, True]).astype(int)
    n = len(df)
    t1 = int(np.ceil(n * 0.33))
    t2 = int(np.ceil(n * 0.66))
    df['tier'] = 'Tier 3'
    df.iloc[:t1, df.columns.get_loc('tier')] = 'Tier 1'
    df.iloc[t1:t2, df.columns.get_loc('tier')] = 'Tier 2'
    return df[['team_name', 'points', 'position', 'tier']]

Complexity

⏰ Time complexity: O(N log N) for sorting and ranking.
🧺 Space complexity: O(N) for output and intermediate columns.

Problem#

Example 1:#

Solution#

Intuition#

Approach#

Code#

MySQL#

PostgreSQL#

Python (pandas)#

Complexity#