Premier League Table Ranking - Practice Coding Problems

Premier League Table Ranking - Problem

Database Unknown

You are tasked with creating a Premier League table ranking system that automatically calculates points and ranks teams based on their match results.

Given a database table TeamStats containing team statistics, you need to:

Calculate total points for each team using Premier League scoring rules
Rank teams based on their points
Handle ties correctly (same points = same rank)
Sort results by points (descending) and team name (ascending)

Scoring System:

🏆 Win = 3 points
🤝 Draw = 1 point
❌ Loss = 0 points

The table structure is:

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| team_id          | int     |
| team_name        | varchar |
| matches_played   | int     |
| wins             | int     |
| draws            | int     |
| losses           | int     |
+------------------+---------+

Note: Teams with identical points must receive the same rank number.

Input & Output

example_1.sql — Basic League Table

$ Input: TeamStats: +----------+-----------+----------------+------+-------+--------+ | team_id | team_name | matches_played | wins | draws | losses | +----------+-----------+----------------+------+-------+--------+ | 1 | Arsenal | 20 | 15 | 3 | 2 | | 2 | Chelsea | 20 | 12 | 5 | 3 | | 3 | Liverpool | 20 | 10 | 8 | 2 | +----------+-----------+----------------+------+-------+--------+

› Output: +----------+-----------+--------+------+ | team_id | team_name | points | rank | +----------+-----------+--------+------+ | 1 | Arsenal | 48 | 1 | | 3 | Liverpool | 38 | 2 | | 2 | Chelsea | 41 | 3 | +----------+-----------+--------+------+

💡 Note: Arsenal: 15×3 + 3×1 = 48 points (Rank 1), Liverpool: 10×3 + 8×1 = 38 points (Rank 2), Chelsea: 12×3 + 5×1 = 41 points gets sorted between Arsenal and Liverpool but ranks as 3rd when ordered by points DESC, team_name ASC

example_2.sql — Teams with Equal Points

$ Input: TeamStats: +----------+-----------+----------------+------+-------+--------+ | team_id | team_name | matches_played | wins | draws | losses | +----------+-----------+----------------+------+-------+--------+ | 1 | Barcelona | 15 | 10 | 2 | 3 | | 2 | Madrid | 15 | 8 | 8 | -1 | | 3 | Atletico | 15 | 8 | 8 | -1 | +----------+-----------+----------------+------+-------+--------+

› Output: +----------+-----------+--------+------+ | team_id | team_name | points | rank | +----------+-----------+--------+------+ | 1 | Barcelona | 32 | 1 | | 3 | Atletico | 32 | 2 | | 2 | Madrid | 32 | 2 | +----------+-----------+--------+------+

💡 Note: Barcelona: 10×3 + 2×1 = 32 points (Rank 1), Both Atletico and Madrid: 8×3 + 8×1 = 32 points each (both Rank 2). When points are tied, teams get the same rank, and results are ordered by team_name alphabetically (Atletico before Madrid).

example_3.sql — Single Team Edge Case

$ Input: TeamStats: +----------+-----------+----------------+------+-------+--------+ | team_id | team_name | matches_played | wins | draws | losses | +----------+-----------+----------------+------+-------+--------+ | 1 | Juventus | 10 | 0 | 0 | 10 | +----------+-----------+----------------+------+-------+--------+

› Output: +----------+-----------+--------+------+ | team_id | team_name | points | rank | +----------+-----------+--------+------+ | 1 | Juventus | 0 | 1 | +----------+-----------+--------+------+

💡 Note: Even with 0 wins, 0 draws, and 10 losses (0×3 + 0×1 + 10×0 = 0 points), Juventus still gets Rank 1 as they are the only team in the table.

Constraints

1 ≤ number of teams ≤ 1000
0 ≤ wins, draws, losses ≤ 50
matches_played = wins + draws + losses
team_name contains only letters and spaces
Points calculation: wins × 3 + draws × 1 + losses × 0

Visualization

Tap to expand

Understanding the Visualization

Calculate Team Points

For each team: Points = Wins×3 + Draws×1 + Losses×0

Sort by Performance

Order teams by points (highest first), then alphabetically by name for ties

Assign Rankings

Use RANK() to give same rank to teams with equal points, skip next ranks

Key Takeaway

🎯 Key Insight: SQL's RANK() window function efficiently handles tie scenarios and maintains proper ranking gaps, making it the optimal choice for leaderboard calculations.

Asked in

E ESPN 25 DK DraftKings 18 FD FanDuel 15 SR Sportradar 12

The optimal solution uses SQL window functions with the RANK() OVER() clause to efficiently calculate team rankings in O(n log n) time. The key insight is calculating points as wins × 3 + draws × 1, then using RANK() OVER (ORDER BY points DESC) to handle ties correctly while maintaining proper sort order by points descending and team name ascending.

Common Approaches

Approach	Time	Space	Notes
✓ Basic SQL with Subquery Ranking	O(n²)	O(1)	Calculate points, then use subquery to count better teams for ranking
Window Function Ranking (Optimal)	O(n log n)	O(1)	Use SQL RANK() window function for efficient and correct ranking

Basic SQL with Subquery Ranking — Algorithm Steps

Calculate points using wins*3 + draws*1 + losses*0
For each team, count teams with higher points using subquery
Add 1 to get the rank (teams with better performance + 1)
Order by points descending, then by team name ascending

Visualization

Tap to expand

Step-by-Step Walkthrough

Calculate Points

For each team, calculate points = wins*3 + draws*1

Count Better Teams

For each team, count how many teams have more points

Assign Rank

Rank = Count of better teams + 1

Code -

solution.c — C

// SQL solution using subquery ranking
#include <stdio.h>
#include <string.h>

char* solution() {
    static char sqlQuery[] = """
        SELECT 
            team_id,
            team_name,
            (wins * 3 + draws * 1 + losses * 0) as points,
            (
                SELECT COUNT(*) + 1 
                FROM TeamStats t2 
                WHERE (t2.wins * 3 + t2.draws * 1) > (t1.wins * 3 + t1.draws * 1)
            ) as rank
        FROM TeamStats t1
        ORDER BY points DESC, team_name ASC;
    """;
    return sqlQuery;
}

int main() {
    printf("%s\n", solution());
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each of n teams, we scan all n teams to count better ones

⚠ Quadratic Growth

Space Complexity

O(1)

No additional space needed beyond result set

✓ Linear Space

28.0K Views

Medium Frequency

~15 min Avg. Time

850 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Basic SQL with Subquery Ranking — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler