Premier League Table Ranking II - Problem
You are given a TeamStats table containing team statistics from the Premier League. Write a SQL query to calculate the points, position, and tier for each team in the league.
Points Calculation:
- 3 points for a win
- 1 point for a draw
- 0 points for a loss
Position Rules:
- Teams are ranked by points (highest to lowest)
- Teams with the same points must be assigned the same position
- When teams are tied, sort by team name alphabetically
Tier Assignment:
- Tier 1: Top 33% of teams
- Tier 2: Middle 33% of teams
- Tier 3: Bottom 34% of teams
- In case of ties at tier boundaries, place tied teams in the higher tier
Return the result ordered by points descending, then by team_name ascending.
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 a team's season statistics
Input & Output
Example 1 — Premier League Teams Ranking
Input 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 | 8 | Tier 3 |
| Liverpool | 11 | 9 | Tier 3 |
| Aston Villa | 9 | 10 | Tier 3 |
💡 Note:
Points calculated: Sheffield United (18×3 + 2×1 = 56), Fulham (18×3 + 1×1 = 55), etc. Everton and Luton Town both have 12 points and share position 7. NTILE(3) divides 10 teams into tiers: top 4 teams in Tier 1, next 4 in Tier 2, bottom 2 in Tier 3.
Example 2 — Edge Case with All Draws
Input Table:
| team_id | team_name | matches_played | wins | draws | losses |
|---|---|---|---|---|---|
| 1 | Arsenal | 10 | 0 | 10 | 0 |
| 2 | Brighton | 10 | 0 | 5 | 5 |
| 3 | Crystal Palace | 10 | 5 | 0 | 5 |
Output:
| team_name | points | position | tier |
|---|---|---|---|
| Crystal Palace | 15 | 1 | Tier 1 |
| Arsenal | 10 | 2 | Tier 2 |
| Brighton | 5 | 3 | Tier 3 |
💡 Note:
Crystal Palace leads with 15 points (5 wins). Arsenal gets 10 points from draws only. With 3 teams, NTILE(3) assigns each team to a different tier.
Constraints
-
1 ≤ team_id ≤ 1000 -
1 ≤ team_name.length ≤ 50 -
0 ≤ matches_played ≤ 100 -
0 ≤ wins, draws, losses ≤ matches_played -
wins + draws + losses = matches_played
Visualization
Tap to expand
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code