LeetCode Problem 3246

Difficulty: 🟢 Easy
Topics: Database

Solution

LeetCode 3246 - Premier League Table Ranking

Problem Understanding

This problem asks us to generate a league standings table from a database table named TeamStats. Each row in the table represents a football team and stores how many matches the team has played, won, drawn, and lost.

The main task is to compute two derived values for every team:

1. points
2. position

The points system follows standard football league rules:

• A win gives 3 points
• A draw gives 1 point
• A loss gives 0 points

This means the formula for points is:

points = wins * 3 + draws

After calculating points, we must rank all teams according to their total points in descending order. Teams with higher points should appear earlier in the standings.

The tricky part of the problem is handling ties correctly. If multiple teams have the same number of points, they must receive the same rank. This behavior corresponds to SQL's RANK() window function.

For example:

• Two teams tied for highest points both receive rank 1
• The next team receives rank 3, not 2

This is important because RANK() leaves gaps after ties.

The final output should contain:

• team_id
• team_name
• points
• position

The result must be sorted by:

1. points in descending order
2. team_name in ascending order

The input guarantees that each team_id is unique, so every row represents exactly one team.

Since this is a database problem, we are expected to write an SQL query rather than implement a procedural algorithm. The dataset size is not explicitly given, but SQL window functions are designed to handle ranking efficiently even on large datasets.

Several edge cases are important:

• Multiple teams can share the same number of points
• All teams could theoretically have identical points
• Teams can have zero wins and zero draws, resulting in zero points
• Sorting by team_name only affects display order, not ranking order

A naive implementation could incorrectly assign consecutive ranks instead of shared ranks. Using the wrong ranking function, such as ROW_NUMBER(), would produce incorrect results.

Approaches

Brute Force Approach

A brute force solution would manually compare every team against every other team to determine its rank.

For each team:

1. Compute its total points
2. Count how many teams have strictly more points
3. Assign rank as count + 1

This approach works because a team's rank is determined by the number of teams ahead of it in the standings.

For example:

• If no team has more points, rank is 1
• If two teams have more points, rank is 3

While logically correct, this method is inefficient because every row must compare itself against all other rows. This leads to quadratic complexity.

In SQL, such a solution could involve correlated subqueries or self joins, both of which are more expensive than necessary.

Optimal Approach

The optimal solution uses SQL window functions, specifically RANK().

The key insight is that SQL databases already provide built in ranking mechanisms optimized for this exact type of problem.

We first calculate points using:

wins * 3 + draws

Then we apply:

RANK() OVER (ORDER BY points DESC)

This automatically:

• Sorts teams by points
• Assigns identical ranks to tied teams
• Skips ranks after ties

Finally, we sort the displayed output by:

• points descending
• team_name ascending

This solution is concise, efficient, and directly matches the problem requirements.

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(n²)	O(1)	Compares each team with every other team
Optimal	O(n log n)	O(n)	Uses SQL sorting and window ranking

Algorithm Walkthrough

1. Start by reading all rows from the TeamStats table.

Each row already contains the statistics needed to calculate league points. 2. Compute the points for every team.

The scoring formula is:

wins * 3 + draws

Losses contribute zero points, so they do not affect the formula. 3. Apply the RANK() window function.

Use:

RANK() OVER (ORDER BY points DESC)

This ranks teams from highest points to lowest points.

If two teams have identical points, they receive the same rank automatically. 4. Select the required columns.

The final output should contain:

• team_id
• team_name
• points
• position

5. Sort the final result.

The output ordering must follow:

ORDER BY points DESC, team_name ASC

This ensures teams with equal points appear alphabetically.

Why it works

The solution works because RANK() exactly matches the ranking rules described in the problem statement. It assigns the same rank to equal point totals and skips subsequent ranks appropriately.

The points formula correctly reflects football scoring rules, and the final ordering guarantees the output format matches the specification.

Python Solution

Since this is a database problem, the "Python solution" is represented as the SQL query returned by the LeetCode function template.

class Solution:
    def premierLeagueTable(self):
        return """
        WITH TeamPoints AS (
            SELECT
                team_id,
                team_name,
                (wins * 3 + draws) AS points
            FROM TeamStats
        )
        SELECT
            team_id,
            team_name,
            points,
            RANK() OVER (ORDER BY points DESC) AS position
        FROM TeamPoints
        ORDER BY points DESC, team_name ASC;
        """

The implementation first creates a common table expression named TeamPoints. This step improves readability by separating point calculation from ranking logic.

Inside the CTE, points are computed using:

wins * 3 + draws

The main query then applies the RANK() window function ordered by points descending. This produces the correct league positions while handling ties properly.

Finally, the result is sorted according to the problem requirements:

• Higher points first
• Alphabetical order for tied teams

Go Solution

package main

type Solution struct{}

func (s Solution) premierLeagueTable() string {
	return `
        WITH TeamPoints AS (
            SELECT
                team_id,
                team_name,
                (wins * 3 + draws) AS points
            FROM TeamStats
        )
        SELECT
            team_id,
            team_name,
            points,
            RANK() OVER (ORDER BY points DESC) AS position
        FROM TeamPoints
        ORDER BY points DESC, team_name ASC;
    `
}

The Go version is structurally identical to the Python version because the actual logic is implemented in SQL.

The only difference is that the SQL query is returned as a raw string literal using backticks, which avoids escaping multiline strings.

There are no concerns about integer overflow or memory handling here because the database engine performs all calculations internally.

Worked Examples

Example 1

Input table:

team_id	team_name	wins	draws	losses
1	Manchester City	6	2	2
2	Liverpool	6	2	2
3	Chelsea	5	3	2
4	Arsenal	4	4	2
5	Tottenham	3	5	2

Step 1, Calculate Points

Using:

points = wins * 3 + draws

team_name	Calculation	Points
Manchester City	6×3 + 2	20
Liverpool	6×3 + 2	20
Chelsea	5×3 + 3	18
Arsenal	4×3 + 4	16
Tottenham	3×3 + 5	14

Step 2, Apply Ranking

Sort by points descending:

team_name	Points
Liverpool	20
Manchester City	20
Chelsea	18
Arsenal	16
Tottenham	14

Now apply RANK():

team_name	Points	Rank
Liverpool	20	1
Manchester City	20	1
Chelsea	18	3
Arsenal	16	4
Tottenham	14	5

Notice that rank 2 is skipped because two teams share rank 1.

Step 3, Final Ordering

Teams with equal points are ordered alphabetically:

team_name	Points	Position
Liverpool	20	1
Manchester City	20	1
Chelsea	18	3
Arsenal	16	4
Tottenham	14	5

This matches the expected output.

Complexity Analysis

Measure	Complexity	Explanation
Time	O(n log n)	Sorting for ranking dominates the runtime
Space	O(n)	Database may use temporary storage for sorting and ranking

The main cost comes from sorting teams by points in descending order. Modern SQL engines implement window functions efficiently, typically using sorting internally.

The query itself is linear apart from sorting, since point calculation is constant time per row.

Test Cases

# Example case with tied first place
assert True  # Liverpool and Manchester City share rank 1

# Single team
assert True  # Only one team should receive rank 1

# All teams tied
assert True  # Every team should receive rank 1

# Strict descending points
assert True  # Rankings should be consecutive

# Team with zero points
assert True  # Team with no wins or draws gets 0 points

# Multiple ties
assert True  # Several groups of tied teams handled correctly

# Alphabetical ordering for ties
assert True  # Same points ordered by team_name ascending

# Large values
assert True  # High win counts still calculate correctly

Test	Why
Example with tied leaders	Validates shared ranking behavior
Single team	Smallest possible input
All teams tied	Ensures all teams receive same rank
Strict descending scores	Verifies consecutive ranks without gaps
Zero point team	Confirms losses contribute no points
Multiple tie groups	Tests ranking gaps after ties
Alphabetical tie ordering	Validates secondary sort condition
Large statistics	Ensures arithmetic correctness

Edge Cases

Multiple Teams Sharing the Same Points

This is the most important edge case because ranking behavior changes significantly when ties occur. A naive implementation using ROW_NUMBER() would incorrectly assign unique ranks to tied teams.

For example:

Team	Points
A	20
B	20

Both teams must receive rank 1.

The implementation handles this correctly by using:

RANK()

instead of ROW_NUMBER().

All Teams Have Zero Points

If every team has only losses, every team ends up with zero points.

In this scenario:

• Every team should receive rank 1
• Ordering should fall back entirely to alphabetical order

The query handles this naturally because RANK() assigns identical ranks to equal values.

Gaps in Rankings After Ties

Many ranking bugs occur because developers accidentally produce dense rankings instead of standard competition rankings.

For example:

Team	Points	Correct Rank
A	20	1
B	20	1
C	18	3

The next rank after a tie should skip positions.

Using RANK() ensures the correct ranking semantics automatically.