Video Game Matchmaking
Electronic Arts makes popular online games that feature multiplayer “matchmaking”. For a special event, EA wants every player to play against every other player exactly once. Additionally, every player has a "level" that indicates their skill and experience in the game.
You're given a table players with the following columns:
player_name(string) – This column contains the username of the player.level(integer) – This represents the skill level of the player, with higher numbers indicating greater skill.
Write a SQL query that generates all possible match-ups between the players in the players table and also calculates the disparity in level between the two players in each matchup. To keep the games evenly matched, we’d like to prevent matches where the players are more than 5 levels apart. Return the list of matches in ascending order of level_disparity.
Your output should contain the following columns: player1, player2 (both populated with player names), level_disparity
Just cross-joining the table will make it such that the players actually meet twice; once as player 1 and player 2, and once as player 2 and player 1.
To fix this, adjust your SQL query to only include combinations where the name of player1 is lexicographically less than the name of player2. This will ensure that each player pair is only included once.
With this query, we leverage a self join on the players table to create all possible matchups, and we use the ABS function to compute the absolute difference in levels between the two players in each matchup, resulting in the "level_disparity". Finally, we filter out games with level_disparity > 5 to exclude uneven matches.
SELECT
p1.player_name AS player1,
p2.player_name AS player2,
ABS(p1.level - p2.level) AS level_disparity
FROM
players p1
JOIN
players p2
ON
p1.player_name < p2.player_name
WHERE
level_disparity <= 5 -- exclude uneven matches
ORDER BY
level_disparity
SELECT p1.player_name AS player1, p2.player_name AS player2, ABS(p1.level - p2.level) AS level_disparity FROM players p1 JOIN players p2 ON p1.player_name < p2.player_name WHERE ABS(p1.level - p2.level) <= 5 ORDER BY level_disparity ASC;