Problem

Table: TeamStats

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

+------------------+---------+
| 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 and rank 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 rank.

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.

Examples

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

Input:
`TeamStats` table:
+---------+-----------------+----------------+------+-------+--------+
| team_id | team_name       | matches_played | wins | draws | losses |
+---------+-----------------+----------------+------+-------+--------+
| 1       | Manchester City | 10             | 6    | 2     | 2      |
| 2       | Liverpool       | 10             | 6    | 2     | 2      |
| 3       | Chelsea         | 10             | 5    | 3     | 2      |
| 4       | Arsenal         | 10             | 4    | 4     | 2      |
| 5       | Tottenham       | 10             | 3    | 5     | 2      |
+---------+-----------------+----------------+------+-------+--------+
Output:
+---------+-----------------+--------+----------+
| team_id | team_name       | points | position |
+---------+-----------------+--------+----------+
| 2       | Liverpool       | 20     | 1        |
| 1       | Manchester City | 20     | 1        |
| 3       | Chelsea         | 18     | 3        |
| 4       | Arsenal         | 16     | 4        |
| 5       | Tottenham       | 14     | 5        |
+---------+-----------------+--------+----------+
Explanation:
* Manchester City and Liverpool both have 20 points (6 wins * 3 points + 2 draws * 1 point), so they share position 1.
* Chelsea has 18 points (5 wins * 3 points + 3 draws * 1 point) and is position 3rd.
* Arsenal has 16 points (4 wins * 3 points + 4 draws * 1 point) and is position 4th.
* Tottenham has 14 points (3 wins * 3 points + 5 draws * 1 point) and is position 5th.
The output table is ordered by points in descending order, then by team_name
in ascending order.

Solution

Method 1

Intuition

We need to compute points for each team and assign ranks (positions) such that teams with the same points share the same rank. SQL window functions (RANK or DENSE_RANK) are ideal for this. In pandas, we can use rank methods.

Approach

  1. Compute points as (wins * 3 + draws * 1).
  2. Assign position using RANK() OVER (ORDER BY points DESC, team_name ASC).
  3. Return the required columns, ordered as specified.

Code

1
2
3
4
5
6
7
8

SELECT team_id, team_name, points, position
FROM (
  SELECT team_id, team_name,
         (wins * 3 + draws) AS points,
         RANK() OVER (ORDER BY (wins * 3 + draws) DESC, team_name ASC) AS position
  FROM TeamStats
) t
ORDER BY points DESC, team_name ASC;
1
2
3
4
5
6
7
8

SELECT team_id, team_name, points, position
FROM (
  SELECT team_id, team_name,
         (wins * 3 + draws) AS points,
         RANK() OVER (ORDER BY (wins * 3 + draws) DESC, team_name ASC) AS position
  FROM TeamStats
) t
ORDER BY points DESC, team_name ASC;
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

import pandas as pd

# Assume df is the TeamStats DataFrame
def premier_league_ranking(df: pd.DataFrame) -> pd.DataFrame:
    df = df.copy()
    df['points'] = df['wins'] * 3 + df['draws']
    df['position'] = df.sort_values(['points', 'team_name'], ascending=[False, True]) \
        .groupby(['points'])['points'].rank(method='min', ascending=False).astype(int)
    df = df.sort_values(['points', 'team_name'], ascending=[False, True])
    return df[['team_id', 'team_name', 'points', 'position']]

Complexity

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