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:
We can use the window function RANK() to calculate each team's points, ranking, and the total number of teams. Then, we can use the CASE WHEN statement to determine the grade of each team.
Pandas
This is the Most Asked FAANG Interview Question! - Two Sum - Leetcode 1 • Greg Hogg • 649,221 views views
Watch 9 more video solutions →Practice Premier League Table Ranking II with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor