+------------------+---------+
|Column Name |Type|+------------------+---------+
| team_id | int || team_name | varchar || matches_played | int || wins | int || draws | int || losses | int |+------------------+---------+
team_id is the uniquekeyfor this table.
This tablecontains 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 win
1 point for a draw
0 points for a loss
Note: Teams with the same points must be assigned the same position.
Tier ranking:
Divide the league into 3 tiers based on points:
Tier 1: Top 33% of teams
Tier 2: Middle 33% of teams
Tier 3: Bottom 34% of teams
In case of ties attier boundaries , place tied teams in the higher tier.
Return the result tableordered bypointsin descending ,** ** and then byteam_nameinascending order.
The query result format is in the following 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:
* Sheffield United has 56points(18 wins *3 points +2 draws *1 point) and isin position 1.* Fulham has 55points(18 wins *3 points +1 draw *1 point) and isin position 2.* Newcastle United has 43points(11 wins *3 points +10 draws *1 point) and isin position 3.* Chelsea has 41points(13 wins *3 points +2 draws *1 point) and isin position 4.* Burnley has 27points(6 wins *3 points +9 draws *1 point) and isin position 5.* Nottingham Forest has 24points(6 wins *3 points +6 draws *1 point) and isin position 6.* Everton and Luton Town both have 12 points,with Everton having 2 wins *3 points +6 draws *1 point, and Luton Town having 4 wins *3 points. Both teams share position 7.* Liverpool has 11points(1 win *3 points +8 draws *1 point) and isin position 9.* Aston Villa has 9points(1 win *3 points +6 draws *1 point) and isin position 10.**Tier Calculation:*****Tier 1:** The top 33% of teams based on points. Sheffield United, Fulham, Newcastle United, and Chelsea fall into Tier 1.***Tier 2:** The middle 33% of teams. Burnley, Nottingham Forest, Everton, and Luton Town fall into Tier 2.***Tier 3:** The bottom 34% of teams. Liverpool and Aston Villa fall into Tier 3.
We need to compute points and assign positions (ranks) for each team, then divide teams into 3 tiers based on points. Ties at tier boundaries must be placed in the higher tier. SQL window functions and careful tier assignment logic are required. In pandas, we can use rank and quantile logic.
WITH Ranked AS (
SELECT team_name, (wins *3+ draws) AS points,
RANK() OVER (ORDERBY (wins *3+ draws) DESC, team_name ASC) ASpositionFROM TeamStats
),
Ordered AS (
SELECT*, ROW_NUMBER() OVER (ORDERBY points DESC, team_name ASC) AS rn
FROM Ranked
),
Counts AS (
SELECTCOUNT(*) AS total FROM TeamStats
),
Tiers AS (
SELECT o.*, c.total,
CEIL(c.total *0.33) AS t1,
CEIL(c.total *0.66) AS t2
FROM Ordered o CROSSJOIN Counts c)
SELECT team_name, points, position,
CASEWHEN rn <= t1 THEN'Tier 1'WHEN rn <= t2 THEN'Tier 2'ELSE'Tier 3'ENDAS tier
FROM Tiers
ORDERBY points DESC, team_name ASC;
WITH Ranked AS (
SELECT team_name, (wins *3+ draws) AS points,
RANK() OVER (ORDERBY (wins *3+ draws) DESC, team_name ASC) ASpositionFROM TeamStats
),
Ordered AS (
SELECT*, ROW_NUMBER() OVER (ORDERBY points DESC, team_name ASC) AS rn
FROM Ranked
),
Counts AS (
SELECTCOUNT(*) AS total FROM TeamStats
),
Tiers AS (
SELECT o.*, c.total,
CEIL(c.total *0.33) AS t1,
CEIL(c.total *0.66) AS t2
FROM Ordered o CROSSJOIN Counts c)
SELECT team_name, points, position,
CASEWHEN rn <= t1 THEN'Tier 1'WHEN rn <= t2 THEN'Tier 2'ELSE'Tier 3'ENDAS tier
FROM Tiers
ORDERBY points DESC, team_name ASC;