Game Leaderboard
HardUnlock detailed company stats for this questionUpgrade
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. Rank by distinct scores: return every player whose highest score is one of the two highest distinct scores on their team. Players with the same score share a place, so some teams will have more than two players in the result. For example, if two players tie at 110 and the next player has 100, all three are returned.
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(), so tied players share a rank and the next score gets the next rank with no gap.- The main query keeps the players with rank 1 or 2, which means everyone holding one of the two highest distinct scores on the team. Using
RANK()instead would skip a rank after a tie, so two players tied for first would leave no one at rank 2.