Game Leaderboard
HardPremium
Zynga is a social game development company known for its popular games played by millions worldwide. Within their games, players can form teams and compete. Each player's performance in different game sessions is recorded as distinct score entries in the database.
You're provided two tables players and scores:
Write a SQL query to return the top 2 players from each team based on their single highest score across all sessions. If multiple players share the same highest score, include all of them, which may result in more than two top players for some teams.
PlayerMaxScoresCTE (Common Table Expression) identifies the highest score achieved by each player across all their game sessions.RankedPlayersCTE then joins theplayerstable with thePlayerMaxScoresCTE to associate the highest scores with their respective players and teams. This CTE also assigns a ranking within each team based on the highest score usingDENSE_RANK().- The main query filters out the players ranked in the top 2 positions (or top positions if there are ties) from each team.
WITH PlayerMaxScores AS (
SELECT
player_id,
MAX(game_score) AS max_score
FROM
scores
GROUP BY
player_id
),
RankedPlayers AS (
SELECT
p.team_id,
p.player_name,
pms.max_score,
DENSE_RANK() OVER (PARTITION BY p.team_id ORDER BY pms.max_score DESC) AS rank
FROM
players p
JOIN
PlayerMaxScores pms ON p.player_id = pms.player_id
)
SELECT
team_id,
player_name,
max_score
FROM
RankedPlayers
WHERE
rank <= 2
ORDER BY
team_id,
rank;
with var1 as (select players.player_id as player_id, player_name, team_id, score_id, max(scores.game_score) as game_score from players join scores on players.player_id = scores.player_id group by players.player_id) select team_id, player_name, max_score from (select team_id, player_name, game_score as max_score, dense_rank() over (partition by team_id order by game_score desc) as score_rank from var1) where score_rank <= 2 order by team_id, score_rank asc