League of Legends: Performance Efficiency Rating (PER)

Find Champions Who Performed Above the Average Performance of Their Tier WITH avg_tier_score_cte AS ( -- Calculate the average champ performance score for each tier SELECT tier, ROUND(AVG(champ_performance_score::NUMERIC), 2) AS avg_tier_score FROM champions_tiers GROUP BY tier ) -- Select each champion only once, comparing their performance score to the average for their tier SELECT DISTINCT ON (c.champion_name) c.tier, c.champion_name, c.champ_performance_score, a.avg_tier_score FROM champions_tiers c JOIN avg_tier_score_cte a ON c.tier = a.tier WHERE c.champ_performance_score > a.avg_tier_score ORDER BY c.champion_name, -- Ensure each champion is distinct c.champ_performance_score DESC; -- Return the highest performance score for each champion ---------------------------------------------------------------------------------------------------------------------------- "B-Tier" "Aurora" 9.48 1.38 "B-Tier" "Cassiopeia" 1.57 1.38 "B-Tier" "Chogath" 11.71 1.38 "S-Tier" "DrMundo" 42.17 39.63 "B-Tier" "Gragas" 6.2 1.38 ......

Top Performers in Games Longer Than Average Duration WITH avg_game_length AS ( SELECT ROUND(AVG(max_minute)::NUMERIC, 2) AS avg_duration FROM ( SELECT MAX(minute) AS max_minute FROM stats_per GROUP BY match_id ) AS game_duration ) SELECT summoner_name, champion_name, ROUND(AVG(per)::NUMERIC, 2) AS avg_performance FROM stats_per WHERE match_id IN ( SELECT match_id FROM stats_per GROUP BY match_id HAVING MAX(minute) > (SELECT avg_duration FROM avg_game_length) ) GROUP BY summoner_name, champion_name ORDER BY avg_performance DESC; ---------------------------------------------------------------------------------------------------------------------------- "white space" "Irelia" 16.40 "white space" "Fizz" 15.82 "Birds cant fly" "Teemo" 15.33 "kimmy" "Hecarim" 14.58 "white space" "Viego" 14.50 "Theoneyahate" "TwistedFate" 13.90 "CarlTheCarry" "Talon" 13.74 ...

Identify champions with the least variance in their PER SELECT champion_name, ROUND(AVG(per)::NUMERIC, 2) AS avg_performance, ROUND(STDDEV(per)::NUMERIC, 2) AS performance_variance FROM stats_per GROUP BY champion_name HAVING COUNT(*) > 10 -- Only include champions with more than 10 matches played ORDER BY performance_variance ASC, avg_performance DESC; ------------------------------------------------------------------------------------------------------------------------ "Braum" 5.44 2.92 "Soraka" 5.10 3.00 "Galio" 5.92 3.37 "Strawberry_Jinx" 5.40 3.63 "Nautilus" 6.62 3.66

Find the average match performance score for champions who won their matches WITH avg_match_performance AS ( -- Calculate the average match performance score for each champion who won SELECT champion_name, AVG(match_performance_score) AS avg_match_performance_score FROM champions_tiers WHERE win = TRUE GROUP BY champion_name ) -- Calculate the overall average of match performance scores and round to 2 decimal places SELECT ROUND(CAST(AVG(avg_match_performance_score) AS NUMERIC), 2) AS average_match_performance_score FROM avg_match_performance; ------------------------------------------------------------------------------------------------------------------------ 16.17

Which champion has the highest assist-to-death ratio (assists per death) for matches they won? WITH assist_to_death_cte AS ( -- Calculate the assist-to-death ratio for each champion SELECT champion_name, ROUND(assists::NUMERIC / deaths, 2) AS assist_to_death FROM champions_tiers WHERE assists > 0 AND deaths > 0 -- Ensures no division by zero AND win = True ) -- Fetch the top 5 champions by assist-to-death ratio SELECT champion_name, assist_to_death FROM assist_to_death_cte ORDER BY assist_to_death DESC LIMIT 1; ------------------------------------------------------------------------------------------------------------------------ "Shen" 11.00

Find the location with the highest total sales SELECT LocationID, SUM(SalesInThousands) AS TotalSales FROM marketing_campaign GROUP BY LocationID ORDER BY TotalSales DESC LIMIT 1;

Find the week-over-week percentage change in sales for a specific location SELECT week, SalesInThousands, LAG(SalesInThousands) OVER (ORDER BY week) AS PreviousWeekSales, (SalesInThousands - LAG(SalesInThousands) OVER (ORDER BY week)) / LAG(SalesInThousands) OVER (ORDER BY week) * 100 AS WeekOverWeekChange FROM marketing_campaign WHERE LocationID = 1;

Identify the most effective promotion type by calculating the average sales per promotion SELECT Promotion, AVG(SalesInThousands) AS AvgSales FROM marketing_campaign GROUP BY Promotion ORDER BY AvgSales DESC;

Find the top 3 locations that benefited the most from promotion type 3 SELECT LocationID, SUM(SalesInThousands) AS TotalSales FROM marketing_campaign WHERE Promotion = 3 GROUP BY LocationID ORDER BY TotalSales DESC LIMIT 3;

Compare sales trends between small, medium, and large market sizes over time SELECT week, MarketSize, SUM(SalesInThousands) AS WeeklySales FROM marketing_campaign GROUP BY week, MarketSize ORDER BY week, MarketSize;

SQL Queries