Premier League Table Ranking - Problem
You are given a table TeamStats containing information about football teams including their wins, draws, and losses.
Your task: Calculate the points and rank for each team in the Premier League. The scoring system is:
- Win: 3 points
- Draw: 1 point
- Loss: 0 points
Important: Teams with the same points must be assigned the same rank.
Return the result ordered by points in descending order, then by team name in ascending order.
Table Schema
TeamStats
| Column Name | Type | Description |
|---|---|---|
team_id
PK
|
int | Unique identifier for each team |
team_name
|
varchar | Name of the football team |
matches_played
|
int | Total number of matches played |
wins
|
int | Number of matches won |
draws
|
int | Number of matches drawn |
losses
|
int | Number of matches lost |
Primary Key: team_id
Note: Each row represents one team's season statistics
Input & Output
Example 1 — Standard League Table
Input Table:
| team_id | team_name | matches_played | wins | draws | losses |
|---|---|---|---|---|---|
| 1 | Arsenal | 38 | 25 | 8 | 5 |
| 2 | Chelsea | 38 | 20 | 10 | 8 |
| 3 | Liverpool | 38 | 22 | 6 | 10 |
Output:
| team_name | points | rank |
|---|---|---|
| Arsenal | 83 | 1 |
| Liverpool | 72 | 2 |
| Chelsea | 70 | 3 |
💡 Note:
Arsenal: 25 wins × 3 + 8 draws × 1 = 83 points (Rank 1)
Liverpool: 22 wins × 3 + 6 draws × 1 = 72 points (Rank 2)
Chelsea: 20 wins × 3 + 10 draws × 1 = 70 points (Rank 3)
Example 2 — Teams with Same Points
Input Table:
| team_id | team_name | matches_played | wins | draws | losses |
|---|---|---|---|---|---|
| 1 | Manchester City | 38 | 28 | 5 | 5 |
| 2 | Arsenal | 38 | 26 | 6 | 6 |
| 3 | Barcelona | 38 | 26 | 6 | 6 |
Output:
| team_name | points | rank |
|---|---|---|
| Manchester City | 89 | 1 |
| Arsenal | 84 | 2 |
| Barcelona | 84 | 2 |
💡 Note:
Manchester City leads with 89 points (Rank 1). Both Arsenal and Barcelona have 84 points, so they both get Rank 2. Note that Arsenal comes first alphabetically in the output.
Constraints
-
1 ≤ team_id ≤ 1000 -
1 ≤ team_name.length ≤ 50 -
0 ≤ wins, draws, losses ≤ 50 -
matches_played = wins + draws + losses
Visualization
Tap to expand
Understanding the Visualization
1
Input Data
Team statistics with wins, draws, losses
2
Calculate Points
Apply scoring system (3-1-0)
3
Assign Ranks
Use RANK() for proper tie handling
Key Takeaway
🎯 Key Insight: RANK() window function automatically handles tied positions in league tables
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code