Window Functions: Ranking

DESC

By adding __________________ to your window function, you can create a rank sorted from largest to smallest.

WITH home AS ( SELECT m.id, t.team_long_name, CASE WHEN m.home_goal > m.away_goal THEN 'MU Win' WHEN m.home_goal < m.away_goal THEN 'MU Loss' ELSE 'Tie' END AS outcome FROM matches AS m LEFT JOIN team AS t ON m.hometeam_id = t.team_api_id), away AS ( SELECT m.id, t.team_long_name, CASE WHEN m.home_goal > m.away_goal THEN 'MU Loss' WHEN m.home_goal < m.away_goal THEN 'MU Win' ELSE 'Tie' END AS outcome FROM matches AS m LEFT JOIN team AS t ON m.awayteam_id = t.team_api_id) SELECT DISTINCT m.date, season, home.team_long_name AS home_team, away.team_long_name AS away_team, m.home_goal, m.away_goal FROM matches AS m LEFT JOIN home ON m.id = home.id LEFT JOIN away ON m.id = away.id WHERE m.season = '2014/2015' AND (home.team_long_name = 'Manchester United' OR away.team_long_name = 'Manchester United');

Combine both the previous two queries together to create a query that shows all matches with Manchester and who their opponent was.

SELECT league.name league, season, AVG(home_goal+away_goal), RANK() OVER(ORDER BY AVG(home_goal + away_goal) DESC) league_rank FROM league INNER JOIN matches ON league.country_id = matches.country_id WHERE season = '2011/2012' GROUP BY league

In this exercise, you will create a data set of ranked matches according to which leagues, on average, score the most goals in a match in season 2011/2012 Complete a column window function so it calculates the rank of average goals scored across all leagues in the database.

WITH Athlete_Medals AS ( SELECT Athlete, COUNT(*) AS Medals FROM olympics GROUP BY Athlete) SELECT Athlete, Medals, RANK() OVER (ORDER BY Medals DESC) AS Rank_N FROM Athlete_Medals ORDER BY Medals DESC; OR SELECT athlete, COUNT(medal) medals, RANK() OVER(ORDER BY count(medal) DESC) ranker FROM olympics GROUP BY athlete ORDER BY medals DESC

Rank each athlete by the number of medals they've earned -- the higher the count, the higher the rank -- with identical numbers in case of identical values.

WITH Athlete_Medals AS ( SELECT year,Country, Athlete, COUNT(*) AS Medals FROM olympics WHERE Country IN ('JPN', 'KOR') AND Year >= 2000 GROUP BY Country, Athlete HAVING COUNT(*) > 1) SELECT Country, Year, Athlete, DENSE_RANK() OVER (PARTITION BY Country ORDER BY Medals DESC) AS Rank_N FROM Athlete_Medals ORDER BY Country ASC, RANK_N ASC; OR SELECT country, year, athlete, medals, DENSE_RANK() OVER(PARTITION BY country ORDER BY medals DESC) ranked FROM (SELECT country, year, athlete, COUNT(medal) medals FROM olympics WHERE year >= 2000 AND country IN ('JPN', 'KOR') GROUP BY athlete) zues WHERE medals > 1

Rank each country's athletes by the count of medals they've earned -- the higher the count, the higher the rank -- without skipping numbers in case of identical values. Solution 1: CTE Query (74 Rows) Solution 2: Subquery (74 Rows)

WITH home AS ( SELECT m.id, t.team_long_name, CASE WHEN m.home_goal > m.away_goal THEN 'MU Win' WHEN m.home_goal < m.away_goal THEN 'MU Loss' ELSE 'Tie' END AS outcome FROM matches AS m LEFT JOIN team AS t ON m.hometeam_id = t.team_api_id), away AS ( SELECT m.id, t.team_long_name, CASE WHEN m.home_goal > m.away_goal THEN 'MU Loss' WHEN m.home_goal < m.away_goal THEN 'MU Win' ELSE 'Tie' END AS outcome FROM matches AS m LEFT JOIN team AS t ON m.awayteam_id = t.team_api_id) SELECT DISTINCT m.date, home.team_long_name AS home_team, away.team_long_name AS away_team, m.home_goal, m.away_goal, RANK() OVER(ORDER BY ABS(home_goal - away_goal) DESC) as match_rank FROM matches AS m LEFT JOIN home ON m.id = home.id LEFT JOIN away ON m.id = away.id WHERE m.season = '2014/2015' AND ((home.team_long_name = 'Manchester United' AND home.outcome = 'MU Loss') OR (away.team_long_name = 'Manchester United' AND away.outcome = 'MU Loss'));

You have one final component of the question left -- how badly did Manchester United lose in each match? In order to determine this, let's add a window function to the main query that ranks matches by the absolute value of the difference between home_goal and away_goal. This allows us to directly compare the difference in scores without having to consider whether Manchester United played as the home or away team! Add the RANK after the away_goal in the main query and make sure to ORDER BY ABS(home_goal - away_goal) DESC) In your filter after season, add home.outcome = MU LOSS for hometeamlongname AND away.outcome = MU LOSS for awayteamlongname.

SELECT m.id, t.team_long_name, season, CASE WHEN m.home_goal > m.away_goal THEN 'MU Win' WHEN m.home_goal < m.away_goal THEN 'MU Loss' ELSE 'Tie' END AS outcome FROM matches AS m LEFT JOIN team AS t ON m.hometeam_id = t.team_api_id WHERE season = '2014/2015' AND t.team_long_name = 'Manchester United';

Your first task is to create the first query that filters for matches where Manchester United played as the home team. Identify if each match was a win, lose, or tie for Manchester United.

SELECT m.id, t.team_long_name, season, CASE WHEN m.home_goal > m.away_goal THEN 'MU Loss' WHEN m.home_goal < m.away_goal THEN 'MU Win' ELSE 'Tie' END AS outcome FROM matches AS m LEFT JOIN team AS t ON m.awayteam_id = t.team_api_id WHERE season = '2014/2015' AND t.team_long_name = 'Manchester United';

Your second task is to create the first query that filters for matches where Manchester United played as the away team. Identify if each match was a win, lose, or tie for Manchester United.