Watch 10 video solutions for Premier League Table Ranking II, a medium level problem involving Database. This walkthrough by Greg Hogg has 649,221 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: TeamStats
+------------------+---------+ | Column Name | Type | +------------------+---------+ | team_id | int | | team_name | varchar | | matches_played | int | | wins | int | | draws | int | | losses | int | +------------------+---------+ team_id is the unique key for this table. This table contains team id, team name, matches_played, wins, draws, and losses.
Write a solution to calculate the points, position, and tier for each team in the league. Points are calculated as follows:
3 points for a win1 point for a draw0 points for a lossNote: Teams with the same points must be assigned the same position.
Tier ranking:
3 tiers based on points:33% of teams33% of teams 34% of teamsReturn the result table ordered by points in descending, and then by team_name in ascending order.
The query result format is in the following example.
Example:
Input:
TeamStats 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 | 7 | Tier 2 | | Liverpool | 11 | 9 | Tier 3 | | Aston Villa | 9 | 10 | Tier 3 | +-------------------+--------+----------+---------+
Explanation:
Tier Calculation: