Nth Ranked Player
Blizzard Entertainment is a renowned gaming company known for its epic games like World of Warcraft, StarCraft, and Overwatch. Players from around the world compete in these games and achieve various rankings based on their performance.
You are given a table named players:
To encourage players to increase time spent in-game, Blizzard want to identify and reward users that just missed the leaderboards e.g. Top 3, Top 10. Write a SQL query that returns the names, scores and ranking of the 4th, 6th, and 11th ranked players in terms of score.
This solution uses a common table expression (CTE) with the RANK() window function to determine the rank of each player based on their score. The final query then selects players that are ranked 4th, 6th, and 11th.
WITH RankedPlayers AS (
SELECT
player_name,
score,
RANK() OVER (ORDER BY score DESC) as ranking
FROM players
)
SELECT player_name, score, ranking
FROM RankedPlayers
WHERE ranking IN (4, 6, 11);
with var1 as (select *, rank() over(order by score desc) as srank from players) select player_name, score, srank as ranking from var1 where srank in (4, 6, 11)