Premier League Table Ranking II - Problem
⚽ Premier League Table Ranking II
You are the data analyst for the Premier League! Your task is to calculate the official league table with points, positions, and tier rankings for all teams.
📊 Points System
- 3 points for a win 🏆
- 1 point for a draw ⚖️
- 0 points for a loss ❌
🏅 Position Rules
Teams are ranked by points (highest first). Teams with identical points share the same position.
🎯 Tier Classification
Divide all teams into 3 performance tiers:
- Tier 1: Top 33% of teams (Champions League contenders)
- Tier 2: Middle 33% of teams (Europa League zone)
- Tier 3: Bottom 34% of teams (Relegation battle)
Important: When teams tie at tier boundaries, they go to the higher tier.
📋 Output Format
Return results ordered by: points DESC, then team_name ASC
Input & Output
example_1.sql — Premier League Table
$
Input:
TeamStats:
+---------+-------------------+----------------+------+-------+--------+
| team_id | team_name | matches_played | wins | draws | losses |
+---------+-------------------+----------------+------+-------+--------+
| 8 | Sheffield United | 20 | 18 | 2 | 0 |
| 5 | Fulham | 31 | 18 | 1 | 12 |
| 7 | Newcastle United | 33 | 11 | 10 | 12 |
| 1 | Chelsea | 22 | 13 | 2 | 7 |
+---------+-------------------+----------------+------+-------+--------+
›
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 |
+-------------------+--------+----------+---------+
💡 Note:
Sheffield United leads with 56 points (18×3 + 2×1), followed by Fulham with 55 points (18×3 + 1×1). All teams shown are in Tier 1 as they're the top performers.
example_2.sql — Tied Teams
$
Input:
TeamStats:
+---------+-------------+----------------+------+-------+--------+
| team_id | team_name | matches_played | wins | draws | losses |
+---------+-------------+----------------+------+-------+--------+
| 10 | Everton | 14 | 2 | 6 | 6 |
| 9 | Luton Town | 5 | 4 | 0 | 1 |
+---------+-------------+----------------+------+-------+--------+
›
Output:
+-------------+--------+----------+---------+
| team_name | points | position | tier |
+-------------+--------+----------+---------+
| Everton | 12 | 1 | Tier 1 |
| Luton Town | 12 | 1 | Tier 1 |
+-------------+--------+----------+---------+
💡 Note:
Both teams have 12 points: Everton (2×3 + 6×1) and Luton Town (4×3 + 0×1). They share position 1. When ordered by team_name, Everton comes first alphabetically.
example_3.sql — Single Team Edge Case
$
Input:
TeamStats:
+---------+-----------+----------------+------+-------+--------+
| team_id | team_name | matches_played | wins | draws | losses |
+---------+-----------+----------------+------+-------+--------+
| 1 | Arsenal | 10 | 5 | 3 | 2 |
+---------+-----------+----------------+------+-------+--------+
›
Output:
+-----------+--------+----------+---------+
| team_name | points | position | tier |
+-----------+--------+----------+---------+
| Arsenal | 18 | 1 | Tier 1 |
+-----------+--------+----------+---------+
💡 Note:
Arsenal has 18 points (5×3 + 3×1). With only one team, it's automatically position 1 and Tier 1 (top 33%).
Constraints
- 1 ≤ number of teams ≤ 100
- 1 ≤ team_id ≤ 1000
- 1 ≤ team_name.length ≤ 50
- 0 ≤ wins, draws, losses ≤ 50
- matches_played = wins + draws + losses
- Team names are unique
Visualization
Tap to expand
Understanding the Visualization
1
Match Results Input
Each team's wins, draws, and losses are recorded
2
Points Calculation
System calculates: Points = Wins × 3 + Draws × 1
3
Ranking Application
Teams are ranked by points (descending), then by name (ascending)
4
Tier Assignment
Top 33% → Tier 1, Middle 33% → Tier 2, Bottom 34% → Tier 3
Key Takeaway
🎯 Key Insight: Window functions like RANK() and NTILE() provide elegant solutions for complex ranking scenarios with ties and percentage-based groupings, all in a single database operation.
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code